[MySQL] 여러 행으로 분리하기 (구분자)

안녕하세요.

오늘은 MySQL 쿼리로 한 row에 들어간 값을 구분자를 활용하여 여러 행으로 나누는 방법을 소개해드리겠습니다.

 

 MySQL 여러 행으로 분리하기

▣ 예시 데이터 :: City

--예시 데이터 만들기
CREATE TABLE city
    (`City` varchar(36))
;
    
INSERT INTO city
    (`City`)
VALUES
    ('Seoul, Paris, Busan, Tokyo, New York')
;

<Before>

City

Seoul, Paris, Busan, Tokyo, New York

 

<After>

value

Seoul

Paris

Busan

Tokyo

New York

 

- 위 예시처럼 구분자 ','를 활용하여 여러 행으로 나눠야 할 때 아래의 쿼리를 활용하시면 됩니다.

select
   SUBSTRING_INDEX(SUBSTRING_INDEX(city.City,',',numbers.n),',',-1) value
          
from 
   (select  1 n union  all  select 2  
    union  all  select  3  union  all select 4 
    union  all  select  5) numbers INNER JOIN city
    on CHAR_LENGTH(city.City) 
      - CHAR_LENGTH(REPLACE(city.City ,  ',' ,  ''))>= numbers.n-1

※ union all 의미 : 구분자로 나뉘어진 행의 개수

 

: 쿼리 설명

1. 행의 개수가 5개이므로 5개의 행을 가진 빈 테이블 numbers를 만들어줌.
2. 구분자 ','의 개수 + 1 (=5) 만큼 변수를 넣기 위해 on 조건에 CHAR_LENGTH() 활용
3. SUBSTRING_INDEX()한 문자열에 SUBSTRING_INDEX(,구분자,-1) 함수를 한 번 더 해준 것은 구분자마다 뽑은 행에서 index에 맞는 값을 추출하기 위해 사용

 

- MSSQL에서는 STRING_SPLIT(column, 구분자)를 활용하면 훨씬 쉽게 사용 가능함.