[MS-SQL] 써보면 편리한 Split 함수 세 개 > db

본문 바로가기

db

[MS-SQL] 써보면 편리한 Split 함수 세 개

페이지 정보

작성자 서방님 댓글 0건 조회 32회 작성일 14-04-24 11:31

본문

프로그래밍 언어에는 문자열 관련 함수가 많이 존재하지요. 그만큼 많이 쓰이기도 합니다.

언어마다 약간의 차이는 있지만 대표적인 함수들은 함수명과 매개변수까지 똑같지요.
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(@문자열,@구분자)

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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(@리스트,@분리자크기)[email protected]분리자
 BEGIN
  SET @리스트[email protected]리스트[email protected]분리자
 END
 SET @리스트[email protected]분리자[email protected]리스트
  
 SET @시작위치 = 1
 SELECT @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치[email protected]분리자크기)
 SET @카운터 = 0
 WHILE (1=1)
 BEGIN
  SET @시작위치 = CHARINDEX (@분리자,@리스트 )
  SELECT @마지막위치 = CHARINDEX (@분리자,@리스트 ,@시작위치[email protected]분리자크기)
        IF @마지막위치 <= 0 BREAK
  INSERT INTO @TB(VAL1) VALUES (SUBSTRING(@리스트,@시작위치[email protected]분리자크기,@마지막위치[email protected]시작위치[email protected]분리자크기))
  SELECT @리스트 = STUFF(@리스트,@시작위치,@분리자크기,'')
  SET @카운터 = @카운터 + 1
 END
 RETURN
END

 

사용 예시

 SELECT * FROM FN_SPLIT('1^2^3^^5^444444','^')

 결과

   03298C3750DE7B7E25364A

 

테이블형 반환 함수이므로 SELECT * FROM 절에  하나의 테이블처럼 사용하면 됩니다.

클라이언트단에서 특정 순서를 가지고 데이터를 문자열로 이어붙여서 파라메터로 넘기고,
이걸 서버단에서 Split으로 쪼개서 사용할 수 있습니다.


물론 2단 이상 트리구조를 가진 데이터라면 XML로 주고받고 하는게 더 좋겠죠.
하지만 간단한 부분을 처리함에 있어서는 상당히 유용함이 분명합니다.

다만, 성능이 그리 뛰어난 함수는 아니므로 수천개 이상 되는 문자열이라면 이 FN_SPLIT함수 외에 다른방법을 사용하는 것을 추천드립니다.
사실 요즘 서버 성능이 워낙 좋아져서,, 이런 함수 좀 돌린다고 해서 무리가 가거나 부하가 많이 걸릴 일은 별로 없습니다만.. ㅎㅎ

 

 

 

 

2. 문자열을 SPLIT한 후 그룹핑하자 #1:  FN_SPLIT_GRP

   SELECT * FROM FN_SPLIT_GRP(@문자열,@구분자,@그룹수)


다음은 자주사용되지는 않지만 Split많으로 뭔가 살짝 아쉬울 때 사용할 수 있는 함수입니다.
예제를 보시는게 가장 이해가 쉬울 것 같습니다.

 

 

사용 예시

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)

결과

  1926153750DE7B7F2A1BCA

 

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을 가장 많이 사용하게 되더군요.
이것도 역시 사용예시를 보시면 이해가 되실 것 같네요.

 

 

 

사용 예시

SELECT * FROM FN_SPLIT_SET
('1^AAA^2^BBB^3^CCC^4^DDD^5^EEE^6^FFF^7^GGG^8^HHH^9^III^','^',2)

결과

  197DBD3950DE7B7F335588


FN_SPLIT_GRP와 마찬가지로 ‘그룹수’ 매개변수가 추가되고, 역시 1~9까지만 사용 가능합니다.
문자열과 결과를 보시면 쉽게 이해가 가시리라 사료됩니다.


웹이던 어플리케이션이던,, 9컬럼 이내의 테이블 형태를 DB에 고스란히 집어 넣으려 할때에 사용하면 매우 편리하죠.

 

 

예를 하나 들어볼께요.

 

2157B93C50DE7B7F2F5F77

 

이런 모양의 데이터를 DB에 고대~~로 집어넣을 일이 있을 때, SPLIT_SETSPLIT_GRP 를 각각 이용한다면

 

FN_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)

 

244B4F3850DE7B8028AC6B


 

FN_SPLIT_SET


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)

1208E04350DE7B801D79D4

 

둘 다 결과값은 동일하지만 문자열을 이어 붙이는 순서가 다릅니다.

GRPSET 둘 중 어느걸 써도 상관이 없다는 말이죠.


테이블에 바로 인서트 하려면 이렇게 하면 되겠죠.

 

1
2
3
4
5
6
7
8
9
10
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

 

실제로는 점수테이블을 저런식으로 넣진 않겠지만 예를들기 위해서 써봤습니다.
언제나 응용은 하기 나름이지요..ㅎ

잘 사용하시길...

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
300
어제
446
최대
592
전체
38,818

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]