'mssql' 태그의 글 목록

mssql

[LocalDB] SQL2012 LocalDB의 Collation 문제 해결 2013.09.11 2
써보면 편리한 MSSQL Split 함수 세 개. 2012.12.29 3

[LocalDB] SQL2012 LocalDB의 Collation 문제 해결

김가별 2013. 9. 11. 17:19

2013. 9. 11. 17:19

[LocalDB] SQL2012 LocalDB의 Collation 문제 해결

MSSQL 2012의 LocalDB 사용 중 Join, Where명령에 대해서 아래와 같은 오류가 발생했다.

Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS" and "Korean_Wansung_CS_AS" in the equal to operation.

데이터베이스의 데이터정렬 형식이 서로 맞지 않아서 작업을 수행할 수 없다는 메시지이다.

왜 이러한 현상이 나타났는가?

MSSQL 2012 LocalDB 인스턴스는 기본적으로 'SQL_Latin1_General_CP1_CI_AS' 형식으로 셋팅되며 이를 변경할 수 없다.

http://technet.microsoft.com/en-us/library/hh510202.aspx

...

The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed. Database-level, column-level, and expression-level collations are supported normally. Contained databases follow the metadata and tempdb collations rules defined by Contained Database Collations.

....

그래서 LocalDB에 데이터베이스를 생성 할 때에 옵션의 데이터정렬값을 설정해 주지 않으면 기본값인 SQL_Latin1_General_CP1_CI_AS 로 설정된다.

이 데이터정렬셋은 한글 데이터가 들어오면 ???? 과 같이 깨져서 들어오고 제대로 처리를 해주지 못한다.

데이터베이스에서 한글을 쓰려면 데이터베이스 생성시 Korean_Wansung_CS_AS 등으로 선택해 주어야 한다.

물론 데이터베이스가 생성된 이후에도 데이터정렬값을 바꿔줄 수는 있는데, 그 전에 생성한 테이블의 컬럼값들 중 string형(nVarchar, varchar 등) 컬럼들은 여전히 SQL_Latin1_General_CP1_CI_AS 로 설정되어 있고, 데이터정렬 설정 이후에 만들어진 테이블, 컬럼에만 Korean_Wansung_CS_AS 이 적용된다.

이 포스팅에서는 이미 SQL_Latin1_General_CP1_CI_AS 로 만들어진 테이블과 컬럼의 Collation 설정을 변경하는 방법을 알아본다.

sp_help TABLENAME

테이블의 정보를 조회할 수 있다. 두번째 결과테이블에 Column리스트업되고 Collation값을 확인할 수 있다.

숫자형 컬럼은 NULL 이고 문자열타입 컬럼에만 현재 Collation값이 보여진다.

이제 변경할 컬럼을 찾았으니 값을 변경해 준다.

ALTER TABLE T_TYPE
ALTER COLUMN 유형명 NVARCHAR(800) COLLATE Korean_Wansung_CI_AS;

ALTER TABLE T_TYPE
ALTER COLUMN 유형텍스트 NVARCHAR(1000) COLLATE Korean_Wansung_CI_AS;

ALTER TABLE T_TYPE
ALTER COLUMN USE_YN NVARCHAR(5) COLLATE Korean_Wansung_CI_AS;

Collation이 잘못 들어간 다른 테이블의 컬럼도 모두 찾아서 위와 같은 방법으로 바꿔준다.

물론 데이터베이스를 생성할 때 미리 Collation값을 지정해 줬다면 위와 같이 번거로운 작업을 하지 않아도 된다.

저작자표시 동일조건

'MS-SQL' 카테고리의 다른 글

[ACCESS] Round 함수의 결과값 반환에 대한 이해. (0)	2013.07.12
[ACCESS] 엑세스의 Round 함수의 버그? 오작동 사례. (0)	2013.07.04
써보면 편리한 MSSQL Split 함수 세 개. (3)	2012.12.29
MS-SQL Server 2012 – LocalDB를 사용해보자. (ADO Connection) (0)	2012.11.03
오류 : 사용자 'sa'의 계정이 현재 잠겨 있으므로 로그인하지 못했습니다. 시스템 관리자가 잠금을 해제할 수 있습니다. (0)	2011.12.29

써보면 편리한 MSSQL Split 함수 세 개.

김가별 2012. 12. 29. 15:08

2012. 12. 29. 15:08

[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 를 각각 이용한다면

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)

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)

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

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

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

잘 사용하시길...

저작자표시 비영리 변경금지

'MS-SQL' 카테고리의 다른 글

[ACCESS] Round 함수의 결과값 반환에 대한 이해. (0)	2013.07.12
[ACCESS] 엑세스의 Round 함수의 버그? 오작동 사례. (0)	2013.07.04
MS-SQL Server 2012 – LocalDB를 사용해보자. (ADO Connection) (0)	2012.11.03
오류 : 사용자 'sa'의 계정이 현재 잠겨 있으므로 로그인하지 못했습니다. 시스템 관리자가 잠금을 해제할 수 있습니다. (0)	2011.12.29
[SQL Query] 차집합의 실 성능 비교. (0)	2011.12.08

PREV 이전 1 NEXT 다음

Lotiony's Blog

mssql

[LocalDB] SQL2012 LocalDB의 Collation 문제 해결

'MS-SQL' 카테고리의 다른 글

써보면 편리한 MSSQL Split 함수 세 개.

'MS-SQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바