[SQL] LOCATE 를 통한 문자열 처음 등장 위치 확인

 

특정 문자열이 몇번째에 등장하는지 알고 싶다면 어떻게 해야할까? 이를 위해 SQL에서는 LOCATE라는 문법을 제공한다. 

 

LOCATE

SELECT LOCATE(substr, str, [pos]);

 

문법은 위와 같다. 검색하고자 하는 서브 스트링 substr을 str에서 검색하고, 해당 substr이 나타나는 위치를 반환해준다. 만약 substr이 str에 없는 경우 0을 반환한다. 한번 예제로 살펴보자. 

 

SELECT LOCATE('test', 'pre test') ;
>> 5

SELECT LOCATE('no', 'pre test') ;
>> 0

 

pos 값 주기

SELECT LOCATE(substr, str, [pos]);

 

pos는 선택적으로 넣을수 있는 값인데, 해당 인자에 값을 주게 되면 해당 인자의 위치에서 부터 검색을 시작하게 된다. 

 

SELECT LOCATE('test', 'test pre test', 5) ;
>> 10

 

test가 1번 위치에 존재하지만, pos에 5 값이 주어졌으므로 5번째 문자부터 검사를 시작한다. 즉 5번째 문자 후로 가장 먼저나오는 test의 위치인 10을 리턴한다.

 

 

LOCATE를 활용하여 문자열 포함 검색하기

	org	value
1	판매	1000
2	소프트웨어 개발	200

LIKE 를 통해서 원하는 문자열이 컬럼 데이터안에 있는지를 검사할 수 있다. 

SELECT * FROM test WHERE org LIKE '%판매%';

 

보통 LIKE는 찾고자 하는 문자열이 서브스트링인 경우에 유용하지만 그 반대의 경우에는 사용할 수가 없다. 예를 들어 컬럼에 있는  데이터가 주어지는 값의 서브스트링인 값들을 찾고 싶을때는 어떻게 해야할까?

 

 즉 '판촉 소프트웨어 개발'의 서브스트링을 해당 테이블에서 찾고 싶다면 어떻게 해야할까? 이때는 LIKE로 검색을 할 수는 없고 LOCATE를 활용하여 검색할 수 있다.

 

SELECT * FROM test WHERE LOCATE(org, '판촉 소프트웨어 개발') > 0 ;

 

즉 위의 쿼리 처럼 substr에 대상 컬럼을 주고, 문자열을 인자로 준 후 LOCATE 함수의 리턴 값이 0보다 클때 값을 리턴해주면 된다.