[MSSQL] PATINDEX 함수 사용법 (패턴 위치 찾기)

SQL Server에서 패턴(정규식)으로 문자열의 위치를 찾을 때는 PATINDEX 함수를 사용하면 된다. 단순 문자열로 위치를 찾을 때는 CHARINDEX 함수를 사용하면 되며, 패턴으로 문자열 위치를 찾을 때는 PATINDEX 함수를 사용하면 된다. PATINDEX 함수를 사용하면 패턴을 사용하여 숫자, 영문자, 한글, 특수문자 등을 찾을 수 있으며, 정규식과 유사한 패턴을 사용하지만 일반적인 정규식은 아니라는 것은 인지하고 있어야 한다.

 

PATINDEX 함수 사용법

 

목차 PATINDEX 기본 사용법 PATINDEX 와일드카드(패턴) 사용법

 

PATINDEX 기본 사용법

SELECT PATINDEX('%SQL%', 'Microsoft SQL Server 2022')

 

 

 

PATINDEX('%찾을문자열%', '대상문자열')

 

찾을 문자열 양쪽에 퍼센트(%) 문자를 붙여서 특정 문자열의 위치를 찾을 수 있다.

해당 문자열이 존재할 경우 해당 문자열의 첫 번째 위치를 반환한다.

PATINDEX 함수의 기본 설정은 대소문자를 구분하지 않고 찾는다.

 

대소문자 구분하여 찾기

SELECT PATINDEX('%SQL%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%sql%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%SQL%', 'Microsoft SQL Server 2022' COLLATE Korean_Wansung_CS_AS) 
     , PATINDEX('%sql%', 'Microsoft SQL Server 2022' COLLATE Korean_Wansung_CS_AS)

 

 

첫 번째(% SQL%)와 두 번째(% sql%) 예제는 동일한 결과를 반환한다. PATINDEX 함수는 대소문자를 구분하지 않고 문자열을 검색하기 때문이다.

 

대문자, 소문자를 구분하여 검색하기 위해서는 대상문자열(대상 칼럼) 뒤에 COLLATE Korean_Wansung_CS_AS 옵션을 사용하면 대소문자를 구분하여 검색한다.

 

PATINDEX 와일드카드(패턴) 사용법

와일드카드	설명
%	0개 이상의 문자를 가진 문자열
_	임의의 한 문자
[ ]	문자 클래스 (대괄호에 포함된 한 문자)
[-]	문자 범위 (0-9, a-z, 가-힝)
[^]	부정 문자 클래스 (대괄호에 포함되지 않은 문자)

 

PATINDEX 함수는 정규식과 유사한 패턴을 사용하여 문자열을 검색할 수 있다. 정규식에서 사용할 수 있는 모든 패턴을 사용할 수는 없고, 위의 표에 명시된 패턴만 사용할 수 있다는 것을 인지해야 한다.

 

퍼센트( % ) 사용법

SELECT PATINDEX('%Microsoft', 'Microsoft SQL Server 2022') 
     , PATINDEX('%SQL', 'Microsoft SQL Server 2022') 
     , PATINDEX('%2022', 'Microsoft SQL Server 2022')

 

 

찾을 문자열의 앞부분만 퍼센트(%)를 붙여서 검색할 경우 대상 문자열의 마지막에 찾을 문자열이 있을 경우만 문자열의 위치를 반환한다.

 

SELECT PATINDEX('Microsoft%', 'Microsoft SQL Server 2022') 
     , PATINDEX('SQL%', 'Microsoft SQL Server 2022') 
     , PATINDEX('2022%', 'Microsoft SQL Server 2022')

 

 

찾을 문자열의 뒷부분만 퍼센트(%)를 붙여서 검색할 경우 대상 문자열의 앞부분에 찾을 문자열이 있을 경우만 문자열의 위치를 반환한다.

 

SELECT PATINDEX('%Microsoft%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%SQL%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%2022%', 'Microsoft SQL Server 2022')

 

 

찾을 문자열의 양쪽에 퍼센트(%)를 붙여서 검색하는 것이 기본 사용법이다. 찾을 문자열이 대상 문자열에 존재하면 해당 문자열의 위치를 반환한다.

 

언더바( _ ) 사용법

SELECT PATINDEX('%S____r%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%S___r%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%Ser__r%', 'Microsoft SQL Server 2022')

 

 

찾을 문자열에 언더바( _ )를 사용하면 언더바의 위치에 해당하는 문자는 모든 문자를 의미한다. 찾을 문자와 언더바를 포함하여 문자열 전체 길이가 일치하면 해당 문자열을 찾는다.

 

문자열 클래스 ( [ ] ) 사용법

SELECT PATINDEX('%[sql]%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%[xql]%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%[xzl]%', 'Microsoft SQL Server 2022')

 

 

문자열 클래스를 사용하여 검색할 경우 대괄호( [ ] ) 안의 문자열은 문자열이 아니라 개별의 문자로 인식하여 검색한다. ( %[sql]% =(동일) %s% OR %q% OR %l% )

 

대괄호 안의 문자 중 일치하는 문자를 찾으면 해당 위치를 반환한다.

 

%[sql]% → Microsoft SQL Server 2022

%[xql]% → Microsoft SQL Server 2022

%[xzl]% → Microsoft SQL Server 2022

 

문자 범위 ( [-] ) 사용법

SELECT PATINDEX('%[0-9]%', 'Microsoft SQL Server 2022') 
     , PATINDEX('%[a-z]%', '마이크로소프트 SQL Server 2022') 
     , PATINDEX('%[가-힝]%', 'Microsoft SQL 서버 2022')

 

 

문자 범위를 사용하면 숫자, 영문자, 한글 등을 검색하여 첫 번째 위치를 반환한다.

 

[0-9] : 모든 숫자 ( 1부터 5까지 찾고 싶으면 [1-5] )

[a-z] : 모든 영문자

[가-힝] : 한글

 

%[0-9]% → Microsoft SQL Server 2022
%[a-z]% → 마이크로소프트 SQL Server 2022
%[가-힝]% → Microsoft SQL 서버 2022

 

SELECT PATINDEX('%[0-9a-z]%', '마이크로소프트 SQL Server 2022') 
     , PATINDEX('%[0-9a-z가-힝]%', '마이크로소프트 SQL Server 2022')

 

 

여러개의 문자 범위를 조합해서 사용할 수 있다.

 

부정 문자 클래스 ( [^] )

SELECT PATINDEX('%[^a-z]%', 'MicrosoftSQL서버2022') 
     , PATINDEX('%[^a-z가-힝]%', 'MicrosoftSQL서버2022') 
     , PATINDEX('%[^0-9a-z가-힝]%', 'MicrosoftSQL서버2022')

 

 

문자 클래스에서 캐럿 기호( ^ )를 사용하여 대괄호 내부의 문자를 제외한 문자의 위치를 찾을 수 있다.

 

%[^a-z]% → MicrosoftSQL서버2022    
%[^a-z가-힝]% → MicrosoftSQL서버2022
%[^0-9a-z가-힝]% → MicrosoftSQL서버2022 (찾지 못함, 특수문자 찾을 때 사용)

 

[MSSQL] CHARINDEX 함수, 특정 문자 찾기

[MSSQL] LIKE 연산자 사용법 (NOT LIKE, 정규식)