MySQL에서 구분자로 사용되는 특정 문자(예: 슬래시 '/')를 기준으로 문자열의 일부를 추출해야 하는 경우가 자주 있습니다. 이때 SUBSTRING_INDEX() 함수를 활용하면 간단하게 해결할 수 있습니다. 이 글에서는 실제 예제를 통해 그 방법을 단계별로 살펴보겠습니다.
1. 테이블 생성
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable
-> (
-> Code varchar(100)
-> );
Query OK, 0 rows affected (1.07 sec)
2. 데이터 삽입
INSERT 명령을 사용하여 테이블에 샘플 레코드를 추가합니다.
mysql> insert into DemoTable values('/101/102/106');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('/110/111/101');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('/111/114/201');
Query OK, 1 row affected (0.47 sec)
mysql> insert into DemoTable values('/111/118');
Query OK, 1 row affected (0.13 sec)3. 저장된 데이터 확인
SELECT 문을 사용하여 테이블의 모든 레코드를 조회합니다.
mysql> select *from DemoTable;
위 쿼리는 다음과 같은 결과를 출력합니다.
+--------------+
| Code |
+--------------+
| /101/102/106 |
| /110/111/101 |
| /111/114/201 |
| /111/118 |
+--------------+
4 rows in set (0.00 sec)
4. 특정 문자 기준으로 문자열 일부 추출하기
이제 슬래시('/') 문자를 기준으로 두 번째 세그먼트 값을 추출하는 쿼리입니다. SUBSTRING_INDEX 함수를 중첩하여 사용하면 원하는 위치의 값을 얻을 수 있습니다.
mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX(Code,'/',3),'/',-1) from DemoTable;
실행 결과는 다음과 같습니다.
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(Code,'/',3),'/',-1) |
+-----------------------------------------------------+
| 102 |
| 111 |
| 114 |
| 118 |
+-----------------------------------------------------+
4 rows in set (0.00 sec)
SUBSTRING_INDEX 함수 동작 원리
위 쿼리가 어떻게 작동하는지 이해하면 다양한 상황에 응용할 수 있습니다.
- SUBSTRING_INDEX(Code, '/', 3): 문자열에서 왼쪽부터 세 번째 '/' 앞까지의 부분 문자열을 반환합니다. 예를 들어 '/101/102/106'은 '/101/102'가 됩니다.
- SUBSTRING_INDEX(..., '/', -1): 앞서 얻은 결과에서 오른쪽부터 첫 번째 '/' 뒤의 부분 문자열을 반환합니다. 즉 '/101/102'에서 '102'만 남게 됩니다.
이처럼 SUBSTRING_INDEX 함수를 중첩하면 구분자로 나뉜 문자열에서 원하는 순번의 값을 손쉽게 추출할 수 있습니다. 음수 인덱스를 사용하면 오른쪽에서부터 계산한다는 점도 기억해 두면 유용합니다.