안녕하세요.
오늘은 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 의미 : 구분자로 나뉘어진 행의 개수
: 쿼리 설명
- 행의 개수가 5개이므로 5개의 행을 가진 빈 테이블 numbers를 만들어줌.
- 구분자 ','의 개수 + 1 (=5) 만큼 변수를 넣기 위해 on 조건에 CHAR_LENGTH() 활용
- SUBSTRING_INDEX()한 문자열에 SUBSTRING_INDEX(,구분자,-1) 함수를 한 번 더 해준 것은 구분자마다 뽑은 행에서 index에 맞는 값을 추출하기 위해 사용
- MSSQL에서는 STRING_SPLIT(column, 구분자)를 활용하면 훨씬 쉽게 사용 가능함.
반응형
'Programming > SQL' 카테고리의 다른 글
[MySQL] rank 순위 구하기 (0) | 2021.07.10 |
---|---|
[MySQL] 엑셀 import & export (0) | 2021.07.08 |
[SQL] 그룹별 문자열 묶기 (group_concat, listag) (1) | 2021.07.04 |
[MySQL] 정규표현식 검색하기 REGEXP, LIKE (2) | 2021.06.30 |
[MySQL] 날짜 요일로 변환하기 (0) | 2021.06.25 |
댓글