MySQL에서 하나의 열에 저장된 값을 여러 부분으로 나누려면 SUBSTRING_INDEX() 함수를 사용하면 됩니다. 이 함수는 지정한 구분자를 기준으로 문자열을 잘라내어 원하는 부분만 반환합니다. 이번 글에서는 실제 예제를 통해 열을 분할하는 과정을 단계별로 살펴보겠습니다.
1. 테이블 생성하기
먼저 이름 데이터를 저장할 테이블을 생성합니다.
mysql> create table DemoTable
-> (
-> Name varchar(40)
-> );
Query OK, 0 rows affected (1.80 sec)
2. 샘플 데이터 삽입하기
INSERT 명령을 사용해 이름과 성이 언더스코어(_)로 연결된 샘플 데이터를 입력합니다.
mysql> insert into DemoTable values('John_Smith');
Query OK, 1 row affected (0.36 sec)
mysql> insert into DemoTable values('Carol_Taylor');
Query OK, 1 row affected (0.28 sec)
mysql> insert into DemoTable values('David_Miller');
Query OK, 1 row affected (0.54 sec)3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+--------------+
| Name |
+--------------+
| John_Smith |
| Carol_Taylor |
| David_Miller |
+--------------+
3 rows in set (0.00 sec)
4. 열 분할 쿼리 실행하기
다음은 언더스코어(_)를 기준으로 이름 부분만 추출하는 쿼리입니다.
mysql> select if(locate('_',Name)=0,'',substring_index(Name, '_', 1)) from DemoTable;실행 결과:
+---------------------------------------------------------+
| if(locate('_',Name)=0,'',substring_index(Name, '_', 1)) |
+---------------------------------------------------------+
| John |
| Carol |
| David |
+---------------------------------------------------------+
3 rows in set (0.00 sec)
쿼리 동작 원리
LOCATE('_', Name)는 Name 열에서 언더스코어가 처음 나타나는 위치를 반환합니다. 구분자가 존재하지 않으면 0을 반환하며, 이 경우 IF 조건문에 따라 빈 문자열('')이 출력되어 오류 없이 안전하게 처리됩니다.
SUBSTRING_INDEX(Name, '_', 1)는 첫 번째 인수인 문자열에서 두 번째 인수인 구분자('_')가 세 번째 인수(1)번째로 나타나기 전까지의 문자열을 반환합니다. 즉, 'John_Smith'에서 'John'만 추출됩니다.
반대로 성(last name) 부분을 추출하고 싶다면 세 번째 인수를 -1로 변경하면 됩니다.
mysql> select substring_index(Name, '_', -1) from DemoTable;