[SQL] INSTR 함수와 SUBSTR 함수를 활용한 문자열 내의 특정 문자 추출

SQL을 활용해 코딩을 하다보면 문자열로 이루어진 컬럼에서 특정 문자를 뽑아내 쓰고싶은 경우가 있다. 대표적으로 핸드폰 전화번호의 맨뒷자리, 이메일에서 @ 표시 뒤, 즉 사이트 관련 정보를 담은 부분 등이 있다. INSTR 함수와 SUBSTR 함수를 활용하여 그 기능을 수행할 수 있다.

 

INSTR(str, substr) : str에서 첫번째 나타나는 부분 문자열 substr의 시작 위치를 리턴함.

ex) INSTR(99-999-9999, '-') = 3 

여기서 -는 문자열 내에서 총 두 번 등장하지만, 첫번째로 등장하는 위치의 인덱스 3이 도출된다

 

SUBSTR(str, position[ , length]) : str의 position부터 length 만큼의 문자열을 리턴함. length가 생략되면, 마지막까지의 문자열을 리턴함. position이 음수면, 뒤(-1)에서부터 카운트함.

ex) SUBSTR( 'abcdefg' , 3, 2) = 'cd'

 

 

대표적인 예제로 '99-999-9999'로 구성된 번호에서 가운데 999부분을 추출하는 코드를 작성해보자

 

예제 1

SET @phone = '99-999-9999';

SELECT SUBSTR(
                @phone,
                INSTR(@phone, '-')+1,
                INSTR( SUBSTR(@phone, INSTR(@phone, '-') +1), '-') -1
)

 

간단하게 SUBSTR( 핸드폰 번호,

                                처음으로 등장하는 '-'의 위치 +1 ,

                                처음으로 등장하는 '-'의 위치 +1 부터 두번째로 등장하는 '-'의 위치 -1)을 해주면 된다.

 

이는 SUBSTR(문자열, 원하는 문자열의 시작위치, 원하는 문자열의 길이) 의 의미를 갖는다.

 

결과는 가운데 번호인 999일 것이다.

 

 

예제 2

 

아래 url에서 파라미터 utm_source의 값을 추출

단, utm_source 값의 길이는 다양함. (예를 들어, google, facebook 등)

 

http://www.example.com/landing-page?utm_source=google&utm_medium=email&utm_campaign=march2012 

 

SET @url = 'http://www.example.com/landing-page?utm_source=google&utm_medium=email&utm_campaign=march2012' ;


SELECT SUBSTR( @url, 
              INSTR(@url,'utm_source') +11,
              INSTR(SUBSTR(@url,INSTR(@url,'utm_source')),'&') -12
              

            
);

 

같은원리로

SUBSTR( 문자열 ,

                       'utm_source'가 시작하는 위치 +11

                      ( 'utm_source='라는 11자리 문자열은 정해져 있으므로 이렇게 설정할 수 있다.) ,

                       'utm_source'가 시작하는 위치부터 처음으로 '&'가 등장하는 위치까지 문자열의 길이를 구하고 거기서 고정                          값인'utm_source'와 '&'을 합친 길이 12를 빼준다.)