[MySQL] SUBSTRING_INDEX를 이용한 SPLIT하기

 데이터베이스로 데이터를 추출하다 보면 substirng이나 left, right 이외에도 split를 활용하여 중간의 데이터만 추출해야 하는 경우가 종종 발생하곤 합니다.

 

ex. 전화번호의 중간 번호 추출하기

      010-1234-5678 ===> 1234

 

ex. 주소에서 시/군/구를 추출해야하는 겨우

      경기도 성남시 분당구 ===> 성남시

 

 이 경우 substirng_index를 변형하여 사용하면 쉽게 추출할 수 있습니다.
 그러면 substring_index는 무엇일까요?

SUBSTRING_INDEX(문자열, 구분자, 구분자index)

 주어진 "문자열(문자의 나열)" 중에서 "구분자" 기준으로 "구분자의 index" 만큼 추출하여 보여주는 것을 말합니다.

 만약 위의 전화번호를 가족 예를 들어, 만약 아래와 같은 Query를 실행하면

SELECT SUBSTRING_INDEX('010-1234-5678', '-', 0)	"INDEX가 0인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', 1)	"INDEX가 1인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', 2)	"INDEX가 2인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', 3)	"INDEX가 3인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', 4)	"INDEX가 4인 경우"

아래와 같은 결과가 나옵니다.

구분자 index가 0인 경우에는 아무 값도 나오지 않으며,

구분자 index가 1인 경우에는 왼쪽 기준으로 '-'가 맨 처음 나오는 문자열의 직전까지 추출됩니다.

구분자 index가 2인 경우에는 왼쪽 기준으로 '-'가 두 번째 나오는 문자열의 직전까지 추출됩니다.

...

 

이 경우 더욱 간단히 이해하려면, 구분자 기준으로 몇 번째 문자열 덩어리 인지로 이해하는게 더 쉬울 겁니다. (각자의 입맛)

 

구분자 index가 0인 경우 구분자 '-' 기준으로 왼쪽 부터 0번째 덩어리까지 추출

구분자 index가 1인 경우 구분자 '-' 기준으로 왼쪽 부터 1번째 덩어리까지 추출

구분자 index가 2인 경우 구분자 '-' 기준으로 왼쪽 부터 2번째 덩어리까지 추출

...

 

그런데 덩어리는 3개인데, 4 이상이면??? 그냥 최대 개수이기 때문에 다 보여준다 생각하면 됩니다.

 

그러면, 왼쪽 기준이 아닌 오른쪽 기준으로 추출하고 싶으면 어떻게 해야 할까요?

이때는 그냥 index에 -만 붙여주면 됩니다.

SELECT SUBSTRING_INDEX('010-1234-5678', '-', -0)	"INDEX가 -0인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', -1)	"INDEX가 -1인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', -2)	"INDEX가 -2인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', -3)	"INDEX가 -3인 경우"
     , SUBSTRING_INDEX('010-1234-5678', '-', -4)	"INDEX가 -4인 경우"

보시는 바와 같이,

구분자 index가 0인 경우 구분자 '-' 기준으로 오른쪽부터 0번째 덩어리까지 추출

구분자 index가 1인 경우 구분자 '-' 기준으로오른쪽부터1번째 덩어리까지 추출

구분자 index가 2인 경우 구분자 '-' 기준으로오른쪽부터2번째 덩어리까지 추출

...

 

그러면, 최초의 목적대로 예시의 전화번호 중간, 주소의 중간을 추출하려면 어떻게 해야 할까요??

이론은 이렇습니다.

"왼쪽부터 중간까지 추출한 다음, 오른쪽부터 첫 번째를 뽑으면 중간이 된다."

 

 그러므로, 아래와 같이 Query를 수행하면 원하는 결과를 추출할 수 있습니다.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('010-1234-5678', '-', 2), '-', -1)	"전화번호 추출"
     , SUBSTRING_INDEX(SUBSTRING_INDEX('경기도 성남시 분당구', ' ', 2), ' ', -1)	"주소 추출"