mysql substring_index 함수를 이용해서 tokenize를 해 봅시다.

 안녕하세요. 이번 시간에는 mysql의 substring_index 함수에 대해서 배워볼 거에요. 문서를 보시면 간단하게 이해할 수 있으실 듯 합니다. 이것이 단독으로 쓰일지는 모르겠는데요. 구분자를 가지고 token을 떨어트릴 때 이용할 수 있습니다.

 

---

 사용 용례를 간단하게 봅시다. target string은 'a|bb|ccc'라고 해 봅시다. 얼핏 봐도 '|'을 구분자로 해서 필드 3개짜리인 데이터가 들어 있는데요.

 

 

 2번째 인자에 '|'을 넣고 3번째 인자에 2를 넣어 봅시다.

 

 

 그러면 놀랍게도 'a|bb'가 떨어지게 되는데요. 2번째 '|'가 나오기 직전까지 잘라버렸음을 알 수 있어요. 3번째 인자에 음수 값이 들어갈 수도 있는데요. 이 경우, 거꾸로 탐색해 버리게 됩니다. 예를 들어 보겠습니다.

 

 

 1번째 인자는 'a|bb|ccc'에요. 2번째 인자는 구분자인 '|'고요. 3번째 인자가 -1인데요. 거꾸로 탐색한다면 우측에서 좌측으로 탐색함을 의미해요. -1이니까, 거꾸로 탐색했을 때, 1번째로 '|'가 나오기 직전까지를 리턴해요. 이게 무슨 소리인지는 아래 결과를 보면 알 수 있습니다.

 

 

 'ccc'가 리턴되었어요. 이는 뒤에서부터 4개를 탐색하면 '|ccc'가 나올 텐데, '|'가 하나 나왔지요. '|'가 1개 나오기 전까지를 리턴하니까, 'ccc'를 리턴하게 됩니다.

 

 

 -2를 넣으면 어떨까요? 똑같이 해석해 보면, -가 붙었으니까 오른쪽부터 왼쪽 방향으로 탐색할 건데요. 2번째로 '|'를 만나기 직전까지를 리턴해요. 그러면 'bb|ccc'를 돌려주겠죠?

 

 

 실행 결과는 위와 같습니다. 파이썬의 음수 인덱싱과 유사한 점이 보이네요.

 

---

 이제 응용 문제를 풀어 봅시다. 필드 n개가 구분자 '|'로 구분되어 있을 때, x번째 필드는 어떻게 뽑을까요? 이것 역시 오늘 배운 mysql 함수인 substring_index를 이용하면 쉽게 하실 수 있는데요. 예를 들어 n이 3이라 하고, 2번째 필드만 뽑는다 해 봅시다. 그러면 먼저 왼쪽에서 우측으로 2번째 '|'가 나오기 직전까지의 string을 뽑아먹을 수 있어요.

 

 

 다음에, 역순으로 보면서 '|'가 첫 번째로 나오는 위치를 뽑아먹으면 됩니다. 즉 3번째 인자에 -1을 넣으면 됩니다.

 

 

 그러면 진한 노란색 부분만 추출되게 됩니다. 이를 sql 쿼리문으로 옮기면 아래와 같습니다.

 

 

 먼저 2번째 줄의 SUBSTRING_INDEX('a|bb|ccc', '|', 2)는 'a|bb'까지만 뽑기 위함입니다. 'a|bb'에서 우측에서 좌측으로 돌릴 때, 1번째에 있는 '|'은 bb 앞에 있는 '|'인데, 이 위치 직전까지 탐색하게 되므로, 최종적인 결과는 'bb'가 나오게 됩니다.

 

 

 실행 결과는 위와 같습니다. 여담으로, string 함수 관련 문서를 보다 보면, multibyte safe라는 말이 엄청나게 나올 텐데요. 

 

 이모지나 한글은 multibyte입니다. multibyte safe인지 보려면, 요런 식으로 한글과 ascii와 이모지를 섞어버리면 됩니다.

 

 

 여객선 emoji를 기준으로 3번째 필드가 잘 뽑혔음을 볼 수 있어요.