[MSSQL] 써보면 편리한 Split 함수 세 개
프로그래밍 언어에는 문자열 관련 함수가 많이 존재하지요. 그만큼 많이 쓰이기도 합니다.
언어마다 약간의 차이는 있지만 대표적인 함수들은 함수명과 매개변수까지 똑같지요.
MS-SQL에도 마찬가지로 기본적으로 문자열 함수가 지원됩니다.
Right, Left, SubString(Mid), Trim, Replace, CharIndex(InStr) ...
그런데 자주 쓰게 되는 문자열함수인 Split 만은 찾아볼 수가 없군요. 저는 Split 을 좋아라하는데..
언어에서 Split 함수는 어떤 문자열을 특정 구분자로 분리해서 배열변수에 담아주는 역할을 합니다.
그런데 MS-SQL에서는 '배열변수' 개념이 없기 때문에 배열변수 대신 테이블변수로 대체해 사용하게 되죠.
사정이 이렇다보니 SQL에서 문자열 Split을 한다면 리턴되는 결과값 변수로 나올수가 없고 테이블이 되어 나올 수밖에 없겠지요.
다른 문자열 함수들처럼 SELECT문에 그냥 갖다쓸수가 없게 됩니다. 그래서 없는가 봅니다.
그렇지만 Split은 없으면 아쉬운 편리한 함수 중 하나입니다.
그래서 사용자정의 함수를 만들어 보았습니다. 우리가 일반적으로 흔히 사용하는(알고있는) Split함수의 용법과 다르지 않습니다.
1. 문자열을 특정 구분자로 단순히 쪼갠다 : FN_SPLIT
SELECT * FROM FN_SPLIT(@문자열,@구분자)
CREATE FUNCTION [dbo].[FN_SPLIT]
(
@리스트 VARCHAR(MAX)
,@분리자 VARCHAR(10)
)
RETURNS @TB TABLE
(
POS int IDENTITY PRIMARY KEY,
VAL1 varchar(200)
)
AS
/*
- '' 값도 반환한다
- 마지막은 분리자로 끝낸다
SELECT * FROM dbo.[FN_SPLIT]('^1^^333^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999','^') A
SELECT * FROM dbo.[FN_SPLIT]('1^^22^^^^^^444444','^^') A
*/
BEGIN
DECLARE
@시작위치 SMALLINT
,@마지막위치 SMALLINT
,@카운터 SMALLINT
,@분리자크기 SMALLINT
SELECT @분리자크기 = LEN(@분리자)
IF RIGHT(@리스트,@분리자크기)!=@분리자
BEGIN
SET @리스트=@리스트+@분리자
END
SET @리스트=@분리자+@리스트
SET @시작위치 = 1
SELECT @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+@분리자크기)
SET @카운터 = 0
WHILE (1=1)
BEGIN
SET @시작위치 = CHARINDEX (@분리자,@리스트 )
SELECT @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+@분리자크기)
IF @마지막위치 <= 0 BREAK
INSERT INTO @TB(VAL1) VALUES (SUBSTRING(@리스트,@시작위치+@분리자크기,@마지막위치-@시작위치-@분리자크기))
SELECT @리스트 = STUFF(@리스트,@시작위치,@분리자크기,'')
SET @카운터 = @카운터 + 1
END
RETURN
END
|
사용 예시 |
SELECT * FROM FN_SPLIT('1^2^3^^5^444444','^') |
|
결과 |
 |
테이블형 반환 함수이므로 SELECT * FROM 절에 하나의 테이블처럼 사용하면 됩니다.
클라이언트단에서 특정 순서를 가지고 데이터를 문자열로 이어붙여서 파라메터로 넘기고,
이걸 서버단에서 Split으로 쪼개서 사용할 수 있습니다.
물론 2단 이상 트리구조를 가진 데이터라면 XML로 주고받고 하는게 더 좋겠죠.
하지만 간단한 부분을 처리함에 있어서는 상당히 유용함이 분명합니다.
다만, 성능이 그리 뛰어난 함수는 아니므로 수천개 이상 되는 문자열이라면 이 FN_SPLIT함수 외에 다른방법을 사용하는 것을 추천드립니다.
사실 요즘 서버 성능이 워낙 좋아져서,, 이런 함수 좀 돌린다고 해서 무리가 가거나 부하가 많이 걸릴 일은 별로 없습니다만.. ㅎㅎ
2. 문자열을 SPLIT한 후 그룹핑하자 #1: FN_SPLIT_GRP
SELECT * FROM FN_SPLIT_GRP(@문자열,@구분자,@그룹수)
다음은 자주사용되지는 않지만 Split많으로 뭔가 살짝 아쉬울 때 사용할 수 있는 함수입니다.
예제를 보시는게 가장 이해가 쉬울 것 같습니다.
CREATE FUNCTION [dbo].[FN_SPLIT_GRP]
(
@리스트 VARCHAR(MAX)
,@분리자 VARCHAR(1)
,@그룹수 SMALLINT
)
RETURNS @TB TABLE
(
POS int IDENTITY PRIMARY KEY,
VAL1 varchar(500) default '',
VAL2 varchar(500) default '',
VAL3 varchar(500) default '',
VAL4 varchar(500) default '',
VAL5 varchar(500) default '',
VAL6 varchar(500) default '',
VAL7 varchar(500) default '',
VAL8 varchar(500) default '',
VAL9 varchar(500) default ''
)
AS
/*
1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999^:한 요소의 집합들로 이루어짐( GROUP형)
====>
1^111^
2^222^
3^333^
4^444^
마지막은 항상 분리자
SELECT * FROM dbo.[FN_SPLIT_GRP]('1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999^','^',1) A
SELECT * FROM dbo.[FN_SPLIT_GRP]('1^2^3^4^5^6^7^8^9^A^AA^AAA^AAAA^AAAAA^AAAAAA^AAAAAAA^AAAAAAAA^AAAAAAAAA^','^',2) A
SELECT * FROM dbo.[FN_SPLIT_GRP]('1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999^1111^2222^3333^4444^5555^6666^7777^8888^9999^','^',3) A
*/
BEGIN
DECLARE @RETURNSTR VARCHAR(MAX)
DECLARE @TEM_STR1 varchar(500)
DECLARE @TEM_STR2 varchar(500)
DECLARE @TEM_STR3 varchar(500)
DECLARE @TEM_STR4 varchar(500)
DECLARE @TEM_STR5 varchar(500)
DECLARE @TEM_STR6 varchar(500)
DECLARE @TEM_STR7 varchar(500)
DECLARE @TEM_STR8 varchar(500)
DECLARE @TEM_STR9 varchar(500)
SET @TEM_STR1=''
SET @TEM_STR2=''
SET @TEM_STR3=''
SET @TEM_STR4=''
SET @TEM_STR5=''
SET @TEM_STR6=''
SET @TEM_STR7=''
SET @TEM_STR8=''
SET @TEM_STR9=''
---- 마지막 분리자가 없으면 분리자를 추가한다
IF RIGHT(@리스트,1)!=@분리자
BEGIN
SET @리스트=@리스트+@분리자
END
DECLARE
@시작위치 SMALLINT
,@마지막위치 SMALLINT
,@카운터 SMALLINT
SET @시작위치 = 0
SET @마지막위치 = -1
SET @카운터 = 0
IF (@그룹수=1)
BEGIN
WHILE CHARINDEX (@분리자,@리스트 ,@시작위치+1) > 0
BEGIN
SET @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+1)
SELECT @TEM_STR1=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
INSERT INTO @TB(VAL1) VALUES (@TEM_STR1)
SET @시작위치 = CHARINDEX (@분리자,@리스트 ,@마지막위치)
SET @카운터 = @카운터+1
END
END
IF (@그룹수>1)
BEGIN
/*
@TT에 일단 모든 데이터를 입력한 후
===> 그룹별로 한 컬럼씩 순서대로 배치한다
*/
--테이블 변수 선언
Declare @TT table
(
POS int IDENTITY (1,1) PRIMARY KEY,
VAL1 varchar(500)
)
Declare @TTL_ARRAYNUM INT
Declare @GROUP_ARRAYNUM INT
WHILE CHARINDEX (@분리자,@리스트 ,@시작위치+1) > 0
BEGIN
SET @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+1)
SELECT @TEM_STR1=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
INSERT INTO @TT(VAL1) VALUES (@TEM_STR1)
SET @시작위치 = CHARINDEX (@분리자,@리스트 ,@마지막위치)
SET @카운터 = @카운터+1
END
SELECT @TTL_ARRAYNUM=COUNT(*) FROM @TT
SET @GROUP_ARRAYNUM=@TTL_ARRAYNUM/@그룹수
Declare @CURR_NUM INT
IF (@그룹수>=2)
BEGIN
/*
일단 그룹갯수 만큼 VAL1 컬럼 데이터 입력
*/
INSERT INTO @TB(VAL1)
SELECT VAL1
FROM @TT A
WHERE
A.POS <= (@GROUP_ARRAYNUM)*1
SET @CURR_NUM=2
UPDATE AA SET VAL2=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=3)
BEGIN
/*
VAL3 컬럼 데이터 업뎃
*/
SET @CURR_NUM=3
UPDATE AA SET VAL3=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=4)
BEGIN
/*
VAL4 컬럼 데이터 업뎃
*/
SET @CURR_NUM=4
UPDATE AA SET VAL4=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=5)
BEGIN
SET @CURR_NUM=5
UPDATE AA SET VAL5=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=6)
BEGIN
SET @CURR_NUM=6
UPDATE AA SET VAL6=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=7)
BEGIN
SET @CURR_NUM=7
UPDATE AA SET VAL7=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=8)
BEGIN
SET @CURR_NUM=8
UPDATE AA SET VAL8=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
IF (@그룹수>=9)
BEGIN
SET @CURR_NUM=9
UPDATE AA SET VAL9=A.VAL1
FROM (
SELECT (A.POS-((@GROUP_ARRAYNUM)*(@CURR_NUM-1))) POS, VAL1
FROM @TT A
WHERE
A.POS > (@GROUP_ARRAYNUM)*(@CURR_NUM-1)
AND
A.POS <= (@GROUP_ARRAYNUM)*(@CURR_NUM)
) A,@TB AA
WHERE
A.POS =AA.POS
END
END
RETURN
END
|
사용 예시 |
SELECT * FROM FN_SPLIT_GRP
('1^2^3^4^5^6^7^8^9^111^222^333^444^555^666^777^888^999
^1111^2222^3333^4444^5555^6666 ^7777^8888^9999^','^',3) |
|
결과 |
 |
FN_SPLIT_GRP 에는 매개변수가 하나 더 추가됩니다.
‘그룹수’ 라는 매개변수로, 1~9까지 쓸 수 있습니다.(그 이상이 필요하다면 다른 방법을 강구하세요.. ㅎㅎ)
이 ‘그룹수’가 들어감으로 인해서 이 함수는 최대 9 x n 형태의 테이블을 반환할 수 있게 됩니다.
위 예시에서 보시다시피 문자열은 다음과 같은 3 그룹으로 나눌 수 있습니다.
1^2^3^4^5^6^7^8^9^
111^222^333^444^555^666^777^888^999^
1111^2222^3333^4444^5555^6666^7777^8888^9999^
그리고 이것을 각각 SPLIT 해서 하나의 열(Column)로 추가해 주는 것이지요.
물론 성능을 고려하고 만들어진 함수는 아닙니다. 어디에 응용하는가는 각자에게 달려 있음을…ㅋㅋ
3. 문자열을 SPLIT한 후 그룹핑하자 #2: FN_SPLIT_SET
SELECT * FROM FN_SPLIT_SET(@문자열,@구분자,@그룹수)
이 함수 역시 문자열 단순 쪼개기에서 한단계 더 나아갑니다.
개인적으로는 FN_SPLIT 과 FN_SPLIT_SET을 가장 많이 사용하게 되더군요.
이것도 역시 사용예시를 보시면 이해가 되실 것 같네요.
CREATE FUNCTION [dbo].[FN_SPLIT_SET]
(
@리스트 VARCHAR(MAX)
,@분리자 VARCHAR(1)
,@그룹수 SMALLINT
)
RETURNS @TB TABLE
(
POS int IDENTITY PRIMARY KEY,
VAL1 varchar(400),
VAL2 varchar(400),
VAL3 varchar(400),
VAL4 varchar(400),
VAL5 varchar(400),
VAL6 varchar(400),
VAL7 varchar(400),
VAL8 varchar(400),
VAL9 varchar(400)
)
AS
/*
1^111^2^222^3^333^4^444^:앞에서 부터 완결된 셋( SET형)
====>
1^111^
2^222^
3^333^
4^444^
마지막은 항상 분리자
SELECT * FROM dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',1) A
SELECT * FROM dbo.[FN_SPLIT_SET]('1^AAA^2^BBB^3^CCC^4^DDD^5^EEE^6^FFF^7^GGG^8^HHH^9^III^','^',2) A
SELECT * FROM dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',3) A
SELECT * FROM dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',4) A
SELECT * FROM dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',4) A
SELECT * FROM dbo.[FN_SPLIT_SET]('1^111^2^222^3^333^4^444^5^555^6^666^7^777^8^888^9^999^','^',5) A
*/
BEGIN
DECLARE @RETURNSTR VARCHAR(400)
DECLARE @TEM_STR1 varchar(400)
DECLARE @TEM_STR2 varchar(400)
DECLARE @TEM_STR3 varchar(400)
DECLARE @TEM_STR4 varchar(400)
DECLARE @TEM_STR5 varchar(400)
DECLARE @TEM_STR6 varchar(400)
DECLARE @TEM_STR7 varchar(400)
DECLARE @TEM_STR8 varchar(400)
DECLARE @TEM_STR9 varchar(400)
SET @TEM_STR1=''
SET @TEM_STR2=''
SET @TEM_STR3=''
SET @TEM_STR4=''
SET @TEM_STR5=''
SET @TEM_STR6=''
SET @TEM_STR7=''
SET @TEM_STR8=''
SET @TEM_STR9=''
---- 마지막 분리자가 없으면 분리자를 추가한다
IF RIGHT(@리스트,1)!=@분리자
BEGIN
SET @리스트=@리스트+@분리자
END
DECLARE
@시작위치 INT
,@마지막위치 INT
,@카운터 INT
SET @시작위치 = 0
SET @마지막위치 = -1
SET @카운터 = 0
WHILE CHARINDEX (@분리자,@리스트 ,@시작위치+1) > 0
BEGIN
SET @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치+1)
IF (@카운터 % @그룹수=0)
BEGIN
SET @TEM_STR1=''
SET @TEM_STR2=''
SET @TEM_STR3=''
SET @TEM_STR4=''
SET @TEM_STR5=''
SET @TEM_STR6=''
SET @TEM_STR7=''
SET @TEM_STR8=''
SET @TEM_STR9=''
END
IF (@카운터 % @그룹수=0)
BEGIN
SELECT @TEM_STR1=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=1)
BEGIN
SELECT @TEM_STR2=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=2)
BEGIN
SELECT @TEM_STR3=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=3)
BEGIN
SELECT @TEM_STR4=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=4)
BEGIN
SELECT @TEM_STR5=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=5)
BEGIN
SELECT @TEM_STR6=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=6)
BEGIN
SELECT @TEM_STR7=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=7)
BEGIN
SELECT @TEM_STR8=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF (@카운터 % @그룹수=8)
BEGIN
SELECT @TEM_STR9=SUBSTRING (@리스트 ,@시작위치+1,@마지막위치- @시작위치-1)
END
IF ((@카운터 % @그룹수)=@그룹수-1)
BEGIN
INSERT INTO @TB(VAL1,VAL2,VAL3,VAL4,VAL5,VAL6,VAL7,VAL8,VAL9) VALUES (@TEM_STR1,@TEM_STR2,@TEM_STR3,@TEM_STR4,@TEM_STR5,@TEM_STR6,@TEM_STR7,@TEM_STR8,@TEM_STR9)
END
SET @시작위치 = CHARINDEX (@분리자,@리스트 ,@마지막위치)
SET @카운터 = @카운터+1
END
RETURN
END
|
사용 예시 |
SELECT * FROM FN_SPLIT_SET
('1^AAA^2^BBB^3^CCC^4^DDD^5^EEE^6^FFF^7^GGG^8^HHH^9^III^','^',2) |
|
결과 |
 |
FN_SPLIT_GRP와 마찬가지로 ‘그룹수’ 매개변수가 추가되고, 역시 1~9까지만 사용 가능합니다.
문자열과 결과를 보시면 쉽게 이해가 가시리라 사료됩니다.
웹이던 어플리케이션이던,, 9컬럼 이내의 테이블 형태를 DB에 고스란히 집어 넣으려 할때에 사용하면 매우 편리하죠.
예를 하나 들어볼께요.
이런 모양의 데이터를 DB에 고대~~로 집어넣을 일이 있을 때, SPLIT_SET과 SPLIT_GRP 를 각각 이용한다면
SELECT * FROM FN_SPLIT_GRP('언어,수리,외국어,사회탐구,사회탐구,사회탐구,언어,수리나,외국어,한국지리,경제지리,세계사,95,90,85,45,40,35,123,120,100,60,55,50,95,91,80,80,70,50,1,2,3,2,4,6',',',6)
SELECT * FROM FN_SPLIT_SET('언어,언어,95,123,95,1,수리,수리나,90,120,91,2,외국어,외국어,85,100,80,3,사회탐구,한국지리,45,60,80,2,사회탐구,경제지리,40,55,70,4,사회탐구,세계사,35,50,50,6',',',6)
둘 다 결과값은 동일하지만 문자열을 이어 붙이는 순서가 다릅니다.
GRP와 SET 둘 중 어느걸 써도 상관이 없다는 말이죠.
테이블에 바로 인서트 하려면 이렇게 하면 되겠죠.
INSERT INTO TBL
SELECT
VAL1 AS 교과
,VAL2 AS 과목
,VAL3 AS 원점수
,VAL4 AS 표준점수
,VAL5 AS 백분위
,VAL6 AS 등급
FROM FN_SPLIT_SET('언어,언어,95,123,95,1,수리,수리나,90,120,91,2,외국어,외국어,85,100,80,3,사회탐구,한국지리,45,60,80,2,사회탐구,경제지리,40,55,70,4,사회탐구,세계사,35,50,50,6',',',6)
ORDER BY POS
실제로는 점수테이블을 저런식으로 넣진 않겠지만 예를들기 위해서 써봤습니다.
언제나 응용은 하기 나름이지요..ㅎ
잘 사용하시길...
Userform Effect.xlsm