MySQL에서 쉼표로 구분된 문자열의 특정 위치 값 추출하기

MySQL SUBSTRING_INDEX()로 쉼표 구분 문자열의 원하는 위치 값 가져오기

쉼표(,)로 구분된 문자열이 저장된 컬럼에서 특정 순번에 해당하는 단일 값을 추출해야 하는 경우가 종종 있습니다. 이럴 때 MySQL의 SUBSTRING_INDEX() 함수를 중첩하여 사용하면 간단하게 해결할 수 있습니다.

SUBSTRING_INDEX() 함수는 지정한 구분자를 기준으로 문자열을 나눈 뒤, 구분자가 N번째 등장하기 전까지의 부분 문자열을 반환합니다. 음수를 사용하면 뒤에서부터 N번째까지의 부분 문자열을 반환합니다. 이 두 가지 특성을 조합하면 쉼표로 구분된 목록에서 정확히 원하는 위치의 값을 얻을 수 있습니다.

1. 테이블 생성

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable1615
    -> (
    -> ListOfSubject text
    -> );
Query OK, 0 rows affected (0.81 sec)

2. 데이터 삽입

INSERT 명령어를 사용해 쉼표로 구분된 과목 목록 데이터를 삽입합니다.

mysql> insert into DemoTable1615 values('Python,Java,MySQL,MongoDB,C,C++,ASP.net');
Query OK, 1 row affected (0.19 sec)

3. 저장된 데이터 확인

SELECT 문으로 테이블의 모든 레코드를 조회합니다.

mysql> select * from DemoTable1615;

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

+-----------------------------------------+
| ListOfSubject                           |
+-----------------------------------------+
| Python,Java,MySQL,MongoDB,C,C++,ASP.net |
+-----------------------------------------+
1 row in set (0.00 sec)

4. 특정 위치의 값 추출 쿼리

이제 쉼표로 구분된 문자열에서 네 번째 위치의 값을 가져오는 쿼리입니다. 내부 SUBSTRING_INDEX()가 앞에서부터 4번째 항목까지의 문자열을 잘라내고, 외부 SUBSTRING_INDEX()에 -1을 적용해 마지막 항목만 남깁니다.

mysql> select substring_index(substring_index(ListOfSubject, ',', 4), ',', -1) as Position from DemoTable1615;

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

+----------+
| Position |
+----------+
| MongoDB  |
+----------+
1 row in set (0.00 sec)

동작 원리 정리

이 쿼리의 동작 방식을 단계별로 살펴보면 다음과 같습니다.

1단계: substring_index(ListOfSubject, ',', 4)는 'Python,Java,MySQL,MongoDB'를 반환합니다. 즉, 앞에서 4번째 구분자까지 포함된 부분 문자열입니다.
2단계: 그 결과에 substring_index(..., ',', -1)을 적용하면 뒤에서 첫 번째 항목인 'MongoDB'만 반환됩니다.