CHARINDEX vs PATINDEX 문자열 검색하는 포인트함수

/*CHARINDEX, PATINDEX 모두 해당 검색문자열의 시작포인트를 찾는 함수이며

, PATINDEX는 와일드카드문자로 패턴을 추가하 찾을 수 있다.

   */

use master

go

SELECT CHARINDEX('important', 'It is important that you maintain your bicycle and keep it in good repair')

GO

SELECT PATINDEX('%important%', 'It is important that you maintain your bicycle and keep it in good repair')

-- %, _ 와일드문자로 패턴 검색이 가능하다

SELECT PATINDEX('%impo__ant%', 'It is important that you maintain your bicycle and keep it in good repair')

-- 대/소문자 Collate 로 설정 가능

SELECT PATINDEX ( '%ein%', 'Das ist ein Test'  COLLATE Latin1_General_BIN) ;

SELECT PATINDEX ( '%Ein%', 'Das ist ein Test'  COLLATE Latin1_General_BIN) ;

SELECT CHARINDEX ( 'TEST', 'This is a Test' COLLATE Latin1_General_BIN);

SELECT CHARINDEX ( 'Test', 'This is a Test' COLLATE Latin1_General_BIN);

[참고문서]

PATINDEX(Transact-SQL)

https://msdn.microsoft.com/ko-kr/library/ms188395.aspx

CHARINDEX(Transact-SQL)

https://msdn.microsoft.com/ko-kr/library/ms186323(v=SQL.120).aspx