하나의 열에 저장된 하이픈(-) 구분 문자열을 여러 개의 개별 열 값으로 분리해야 하는 경우가 종종 있습니다. 이럴 때 SUBSTRING_INDEX() 함수를 활용하면 간단하게 해결할 수 있습니다.
1. 테스트 테이블 생성
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable1962
(
EmployeeInformation text
);
Query OK, 0 rows affected (0.00 sec)2. 샘플 데이터 삽입
INSERT 명령을 사용하여 '사번-이름-나이' 형식의 데이터를 입력합니다.
mysql> insert into DemoTable1962 values('101-John-29');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1962 values('102-David-35');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1962 values('103-Chris-28');
Query OK, 1 row affected (0.00 sec)3. 저장된 데이터 확인
SELECT 문으로 전체 레코드를 조회해 보겠습니다.
mysql> select * from DemoTable1962;
실행 결과는 다음과 같습니다.
+---------------------+ | EmployeeInformation | +---------------------+ | 101-John-29 | | 102-David-35 | | 103-Chris-28 | +---------------------+ 3 rows in set (0.00 sec)
4. SUBSTRING_INDEX()로 값 분리하기
이제 하나의 열에 담긴 값을 사원 ID, 이름, 나이 세 개의 열로 분리하여 조회하는 쿼리입니다.
mysql> select substring_index(EmployeeInformation, '-', 1) as EmployeeId,
substring_index(substring_index(EmployeeInformation,'-',2),'-',-1) AS EmployeeName,
substring_index(substring_index(EmployeeInformation,'-',-2),'-',-1) AS EmployeeAge
from DemoTable1962;실행 결과는 다음과 같습니다.
+------------+--------------+-------------+ | EmployeeId | EmployeeName | EmployeeAge | +------------+--------------+-------------+ | 101 | John | 29 | | 102 | David | 35 | | 103 | Chris | 28 | +------------+--------------+-------------+ 3 rows in set (0.00 sec)
쿼리 동작 원리
SUBSTRING_INDEX(문자열, 구분자, 위치) 함수는 지정한 구분자를 기준으로 문자열을 잘라내는 역할을 합니다.
- EmployeeId:
substring_index(EmployeeInformation, '-', 1)— 왼쪽에서 첫 번째 하이픈 앞까지의 문자열, 즉 사번을 추출합니다. - EmployeeName:
substring_index(substring_index(EmployeeInformation,'-',2),'-',-1)— 왼쪽에서 두 번째 하이픈 앞까지 자른 후, 다시 오른쪽에서 첫 번째 부분만 가져와 가운데 값인 이름을 추출합니다. - EmployeeAge:
substring_index(substring_index(EmployeeInformation,'-',-2),'-',-1)— 오른쪽에서 두 번째 하이픈 기준으로 자른 뒤 마지막 부분을 가져와 나이를 추출합니다.
이처럼 SUBSTRING_INDEX()를 중첩해서 사용하면 구분자로 연결된 문자열 데이터를 손쉽게 여러 열로 분리할 수 있으며, 정규화되지 않은 레거시 데이터를 처리할 때 특히 유용합니다.