MySQL에서 특정 문자로 문자열 SPLIT 하기 - SUBSTRING_INDEX

MYSQL에서는 특정 문자를 기준으로 SPLIT해서 해당 값을 여러 열로 나누어야 할 일이 발생했을때 사용하는 방법을 작성해보겠습니다.

 

위와같이 THRESHOLD라는 컬럼이 파이프라인( | )으로 이루어져 있다고 했을때 해당 " | "를 기준으로 컬럼을 나누고 싶다고 가정했을때

 

SUBSTRING_INDEX(문자열, 구분자, 구분자의 index)

 

SUBSTRING_INDEX함수를 통해서 해당 값을 나눌 수 있습니다.

 

문자열을 시작 or 끝부터 index번째 구분자까지 자르는 것입니다.

 

index가 양수일 경우 문자열의 첫부분부터,

index가 음수일 경우 문자열의 뒷부분부터 자르게됩니다.

 

THRESHOLD의 문자열 값을 구분자 " | "에 따라 나누어 출력한다고 했을때

 

THRESHOLD 값 예)  1000|2000|3000

구문	결과
SUBSTRING_INDEX('THRESHOLD', '|', 1)	1000
SUBSTRING_INDEX('THRESHOLD', '|', 2)	1000|2000
SUBSTRING_INDEX('THRESHOLD', '|', 3)	1000|2000|3000
SUBSTRING_INDEX('THRESHOLD', '|', -1)	3000
SUBSTRING_INDEX('THRESHOLD', '|', -2)	2000|3000
SUBSTRING_INDEX('THRESHOLD', '|', -3)	1000|2000|3000

 

이렇게만 사용하면 원래 알고 있던 SPLIT 함수와는 다르게 출력이 됩니다.

 

SPLIT은 배열으로 반환되어 가운데 문자열(예시문자열에서는 2000)이라는 값을 가져오고 싶을때

자바를 예로들면

arr = split("1000|2000|3000", "|");

arr[1] --> "2000"

이런식으로 바로 가져올 수 있는데,

 

SUBSTRING_INDEX 함수는 문자열의 첫부분 혹은 끝부분부터 자르는 함수이기때문에

SPLIT처럼단순하게 출력할 수 없습니다.

 

대신, SUBSTRING을 한번 더 씌우는 방식으로 중간 문자열을 가져올 수 있습니다.

SUBSTRING_INDEX(SUBSTRING_INDEX('THRESTHOLD' '|', -2), '|', 1)

 

만약 |를 기준으로 3개의 구간으로 나누어 A, B, C라는 이름의 컬럼으로 새롭게 쿼리를 조회하고 싶다면

SELECT 
	SUBSTRING_INDEX(threshold, '|', 1) AS A,
	SUBSTRING_INDEX(SUBSTRING_INDEX(threshold, '|', -2),'|',1) AS B,
	SUBSTRING_INDEX(threshold, '|', -1) AS C
FROM DBMS

위와같이 사용할 수 있습니다.