MySQL에서 단일 열에 저장된 성과 이름을 두 개의 새로운 열로 분리하는 방법

MySQL에서 하나의 열에 함께 저장된 성(last name)과 이름(first name)을 두 개의 새로운 열로 분리하려면 SUBSTRING_INDEX() 함수와 REPLACE() 함수를 함께 활용하면 됩니다. 먼저 예제에 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable (Name varchar(100));
Query OK, 0 rows affected (0.53 sec)

insert 명령을 사용하여 테이블에 샘플 데이터를 삽입합니다. 여기서는 이름과 성이 하나의 문자열로 함께 저장되어 있습니다.

mysql> insert into DemoTable values('Chris | Bob Brown');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Carol | Robert Taylor');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('Sam | David Miller');
Query OK, 1 row affected (0.13 sec)

select 문을 사용하여 테이블의 모든 레코드를 확인합니다.

mysql> select *from DemoTable;

위 쿼리는 다음과 같은 결과를 출력합니다.

+-----------------------+
| Name                  |
+-----------------------+
| Chris | Bob Brown     |
| Carol | Robert Taylor |
| Sam   | David Miller |
+-----------------------+
3 rows in set (0.00 sec)

다음은 MySQL에서 성과 이름을 두 개의 새로운 열로 분리하는 쿼리입니다.

mysql> SELECT REPLACE(Name, SUBSTRING_INDEX(Name, ' ', -1), '')
    AS FirstName, SUBSTRING_INDEX(Name, ' ', -1) AS LastName from DemoTable;

실행 결과는 다음과 같습니다.

+-----------------+----------+
| FirstName       | LastName |
+-----------------+----------+
| Chris | Bob     | Brown    |
| Carol | Robert  | Taylor   |
| Sam   | David   | Miller   |
+-----------------+----------+
3 rows in set (0.00 sec)

쿼리 동작 원리

SUBSTRING_INDEX(Name, ' ', -1): 문자열에서 마지막 공백 뒤에 위치한 부분, 즉 성(LastName)을 추출합니다. 세 번째 인자로 음수(-1)를 지정하면 문자열의 오른쪽 끝에서부터 구분자를 찾게 됩니다.

REPLACE(Name, SUBSTRING_INDEX(Name, ' ', -1), ''): 전체 문자열에서 위에서 추출한 성 부분을 빈 문자열('')로 치환하여, 성을 제외한 나머지 이름(FirstName)만 남깁니다.

이처럼 두 함수를 조합하면 별도의 복잡한 처리 없이 하나의 열에 저장된 이름 데이터를 손쉽게 분리할 수 있습니다. 참고로 이 방식은 SELECT 문에서 바로 결과를 조회하는 예제이며, 실제 운영 환경에서는 분리된 값을 새로운 열에 저장하려면 ALTER TABLE로 열을 추가한 후 UPDATE 문과 함께 이 로직을 적용하면 됩니다.