#title Split 패턴
[[TableOfContents]]

==== 일반적인 사항 ====
가끔씩 Split이란 단어로 여러 솔루션을 접하고는 한다. 오라클은 InStr함수가 존재하여 매우 쉽게 해결할 수 있다. 하지만 MS-SQL Server는 좀 처럼 쉽지가 않다. 대부분의 경우 UI에서 여러 데이터 항목을 체크하여 저장 프로시저로 '1;3;5;7;10'과 같은 형태의 매개변수로 사용된다. 필자의 홈페이지를 포함하여 커뮤니티에는 '1;3;5;7;10'로 합치고, 이것을 5개의 행으로 다시 만드는 솔루션으로 charindex를 이용하여 루프를 돌리는 솔루션이 많이 있다. 특히 2000 버전에서는 루프 자체가 쥐약이기 때문에 더욱 성능을 떨어뜨렸다. 하지만 SQL Server 2005에서는 XML에 대한 여러 지원으로 이를 쉽게 풀 수 있다. 2005에서는 함수의 수행성능도 눈에 띄게 향상되었기 때문에 2000에 비해서는 함수내의 루프를 이용한 솔루션도 괜찮다. XML과 함수 중에서는 함수가 더 낫다. 그러나 XML을 이용하는 것은 소스 코드의 이해가 쉽다. 또한 숫자테이블을 이용한 Join 솔루션도 있다. 

==== 솔루션1 ====
{{{
--테이블 준비
--drop table #temp
create table #temp(t char(1), val int)
insert #temp values('a', 1)
insert #temp values('a', 2)
insert #temp values('a', 3)
insert #temp values('b', 4)
insert #temp values('b', 5)
insert #temp values('b', 6)
insert #temp values('c', 7)
go

select
	t
,	stuff((
		select ';' + cast(val as varchar(100)) as 'text()' 
		from #temp where a.t = t for xml path('')),1,1,'') rs
from (select distinct t from #temp) a

/* 결과
t    rs
---- ----------------
a    1;2;3
b    4;5;6
c    7
*/
}}}

==== 솔루션2 ====
{{{
--테이블준비
select
	t
,	stuff((
		select ';' + cast(val as varchar(100)) as 'text()' 
		from #temp where a.t = t for xml path('')),1,1,'') rs
into #xml
from (select distinct t from #temp) a

--쪼개기
select
	x.t
,	convert(varchar(10), y.item.query('text()')) val
from (
	select
		t
	,	convert(xml, '<r>' + replace(rs, ';', '</r><r>') + '</r>') xitem
	from #xml) x cross apply x.xitem.nodes('/r') as y(item)

/* 결과
t    val
---- ----------
a    1         
a    2         
a    3         
b    4         
b    5         
b    6         
c    7     
*/
/*
--다중값 매개변수 처리
DECLARE @GameSeqs varchar(5000);
SET @GameSeqs = '34;36'
SET @GameSeqs = '<r>' + replace(@GameSeqs, ';', '</r><r>') + '</r>';
SELECT
	CONVERT(varchar(10), y.item.query('text()')) val
FROM (SELECT CONVERT(xml, @GameSeqs) xitem) x
	CROSS APPLY x.xitem.nodes('/R') as y(item)
*/
}}}

==== 쉽게 .. ====
{{{
select
	convert(xml, '<r>' + replace(row, ',', '</r><r>') + '</r>').value('/r[1]', 'varchar(50)') row1
,	convert(xml, '<r>' + replace(row, ',', '</r><r>') + '</r>').value('/r[2]', 'varchar(50)') row1
,	convert(xml, '<r>' + replace(row, ',', '</r><r>') + '</r>').value('/r[3]', 'varchar(50)') row1
from (select '1,2,3,4,5' row) t
}}}

{{{
select
	convert(xml, row).value('/r[1]', 'varchar(50)') row1
,	convert(xml, row).value('/r[2]', 'varchar(50)') row2
,	convert(xml, row).value('/r[3]', 'varchar(50)') row3
,	convert(xml, row).value('/r[3]', 'varchar(50)') row4
from (select '<r>' + replace('기타,스타크래프트,10,0', ',', '</r><r>') + '</r>' row) t
}}}

==== 솔루션3 ====
{{{
----CSV형태의 컬럼을 대상으로 쿼리하기 
IF OBJECT_ID('dbo.instr') IS NOT NULL 
   DROP FUNCTION dbo.instr 
GO 

--아쉽게도 오라클의 instr 기능을 완벽히 수행하는 함수가 MSSQL은 없다. 
--사용자정의함수를 만들자. 
CREATE FUNCTION dbo.instr(@str VARCHAR(8000), @delimiter VARCHAR(100), @pos smallint) 
RETURNS VARCHAR(8000) 
AS  
BEGIN 
   DECLARE  
       @i smallint 
   ,   @p1 smallint 
   ,   @p2 smallint 
   SET @i = 1  
   SET @p2 = 1 
    
   WHILE(@i <= @pos) 
   BEGIN 
       SET @p1 = @p2 
       SET @p2 = CHARINDEX(@delimiter, @str, CASE WHEN @p2 > 1 THEN @p2 + 1 ELSE @p2 END) 
       SET @i = @i + 1 
   END 

   SET @p1 = CASE WHEN @i > 2 THEN @p1 + 1 ELSE @p1 END 
   SET @p2 = CASE WHEN @p2 > 0 THEN @p2 ELSE 8000 END 
   RETURN SUBSTRING(@str, @p1, @p2 - @p1) 
END 
GO 

CREATE TABLE #temp(m_str VARCHAR(100)) 
INSERT #temp VALUES('aaa;bbb;ccc;ddd') 
INSERT #temp VALUES('111;222;333;444') 
INSERT #temp VALUES('1;2') 

CREATE TABLE #dumy(seq INT) 
INSERT #dumy VALUES(1) 
INSERT #dumy VALUES(2) 
INSERT #dumy VALUES(3) 
INSERT #dumy VALUES(4) 
INSERT #dumy VALUES(5) 
INSERT #dumy VALUES(6) 
INSERT #dumy VALUES(7) 
INSERT #dumy VALUES(8) 
INSERT #dumy VALUES(9) 
INSERT #dumy VALUES(10) 

--CSV형태의 컬럼의 구분기호 개수로 전체 문자열 구분개수를 알아낸다. 
--2000버전에서 테스트: 약6분, 2005버전에서 약 25초(데이터는 'aaa;bbb;ccc;ddd;aaa;bbb;ccc;ddd' 10만건 
--len(m_str) - len(replace(m_str, ';', '')) + 1 
--아..글고..문자열이 길어지면 허당
--글고..clr을 이용하면 매우 좋아..이런 경우는..
SELECT 
   dbo.instr(m_str, ';', seq) m_str 
FROM ( 
   SELECT  
       LEN(m_str) - LEN(REPLACE(m_str, ';', '')) + 1 str_cnt 
   ,   m_str 
   FROM #temp) a INNER JOIN #dumy b 
ON a.str_cnt >= b.seq 
GO 
}}}


==== 또 다른 형태: 구분자로 구분된 문자열 숫자의 합계 구하기 ====
{{{ 
drop table #temp 

create table #temp 
(
	idx int 
,	vals varchar(4000)
)
go

insert into #temp values (1, '1/2')
insert into #temp values (2, '1/2/3/4/5/6/7/8/9/10')
insert into #temp values (3, '1')
insert into #temp values (4, '40/30/20/10')
insert into #temp values (5, '4230/3/210/11110')
go

with dumy
as
(
        select 1 seq
        union all
        select seq + 1 from dumy
        where seq + 1 <= 2000
), temp
as
(
	select 
		a.idx
	,	b.seq
	,	a.vals
	,	b.pos1
	,	case when b.pos2 = 0 then len(a.vals)+1 else b.pos2-1 end pos2
	,	b.delimiter 
	from #temp a
		cross apply(
			select 
				seq
			,	charindex('/', '/' + a.vals, seq) pos1
			,	charindex('/', '/' + a.vals, seq+1) pos2
			,	substring('/' + a.vals, seq, 1) delimiter
			from dumy
			where seq <= len(a.vals) -- len(replace(a.vals, '/', ''))+1
		) b
	where 1=1
	and b.delimiter = '/' -- 이 조건을 빼면 문자열 세로 -> 가로로
	--and a.idx = 5
)
select 
	idx
,	sum(convert(int, substring(vals, pos1, pos2 - pos1)))
from temp
group by
	idx
order by idx
option (maxrecursion 0);
}}}

==== 문자열 split ====
{{{
/*
--drop table dummy
;with dummy
as
(
        select 1 seq
        union all
        select seq + 1 from dummy
        where seq + 1 <= 100000
)   
select * 
into dummy
from dummy
option (maxrecursion 0)
go

create unique clustered index cix
on dummy(seq)
go
*/


;with temp
as
(
    select 1 id, '1,200,3,4,5' string union all
    select 2 id, '2,3,5,100,20'
)
select 
    a.id
,   a.string
--,   b.seq
--,   charindex(',', a.string, b.seq+1)
,   replace(substring(a.string, b.seq, case when charindex(',', a.string, b.seq+1) = 0 then 100 else charindex(',', a.string, b.seq+1) end - b.seq), ',', '') split
,   b.idx
from temp a
    cross apply (
        select 
            seq
        ,   row_number() over(order by seq) idx
        from dummy
        where len(a.string) >= seq
        and substring(',' + a.string, seq, 1) = ','
    ) b
where 1=1
}}}
==== 더 좋은 성능 ====
위에서 제시한 솔루션보다 더 좋은 성능을 발휘하는 솔루션은 CLR을 이용하는 것이다. 성능은 필자가 아는 솔루션 중에는 단연 최고다. 하지만 CLR 버전에 따른 차이의 극복 및 프로그램 유지보수의 문제가 있다. 자세한 사항은 아래의 첨부된 파일을 참고하기 바란다. 

attachment:UFN_CLR_Split.zip
attachment:Split패턴/CLR_UAgg__RowsToCol.zip

사용방법은 다음과 같다.
{{{
------------------
--Agg_RowsToColumn
------------------
--관련함수: UFN_CLR_Split

CREATE TABLE BookAuthors
(
   BookID   int       NOT NULL,
   AuthorName    nvarchar(200) NOT NULL
);

INSERT BookAuthors VALUES(1, 'Johnson');
INSERT BookAuthors VALUES(2, 'Taylor');
INSERT BookAuthors VALUES(3, 'Steven');
INSERT BookAuthors VALUES(2, 'Mayler');
INSERT BookAuthors VALUES(3, 'Roberts');
INSERT BookAuthors VALUES(3, 'Michaels');

SELECT BookID, dbo.Agg_RowsToColumn(AuthorName)
FROM BookAuthors
GROUP BY BookID;

/*결과

BookID      
----------- -------------------------
1           Johnson
2           Taylor,Mayler
3           Roberts,Michaels,Steven

(3 row(s) affected)

*/


---------------
--UFN_CLR_Split
---------------
--관련함수: Agg_RowsToColumn
select * from dbo.UFN_CLR_Split('a;b;c;d;e;f;g', ';');

/* 결과

SplitCol
-----------------------
a
b
c
d
e
f
g

(7 row(s) affected)

*/
--Agg_RowsToColumn함수와 UFN_CLR_Split 함수를 연합하여 응용
WITH exTable(BookID, SplitCol) AS
(
	SELECT 
		BookID
	,	dbo.Agg_RowsToColumn(AuthorName) SplitCol
	FROM BookAuthors
	GROUP BY BookID
)
SELECT 
	A.BookID
,	CONVERT(NVARCHAR(30), A.SplitCol) SplitCol_A
,	CONVERT(NVARCHAR(30), B.SplitCol) SplitCol_B
FROM exTable A CROSS APPLY dbo.UFN_CLR_Split(A.SplitCol, ',') B

/* 결과

BookID      SplitCol_A                     SplitCol_B
----------- ------------------------------ ------------------------------
1           Johnson                        Johnson
2           Taylor,Mayler                  Taylor
2           Taylor,Mayler                  Mayler
3           Roberts,Michaels,Steven        Roberts
3           Roberts,Michaels,Steven        Michaels
3           Roberts,Michaels,Steven        Steven

(6 row(s) affected)
*/
}}}


==== 또 다른 예제 ====
다음과 같이 row가 구성되어 있다. 구분자고 '/'를 쓰는데, 가장 큰 값을 구하고 싶다.
{{{
1086505750/563631750
100
1114881625/583485375
139066000/1100000000/993888382
293533042/367173400
110000000/110000000/111233343/234234277
}}}

다음가 같이 하면 된다. 
{{{
;with temp
as
(
select '1086505750/563631750' x union all
select '100' union all
select '1114881625/583485375' union all
select '139066000/1100000000/993888382' union all
select '293533042/367173400' union all
select '110000000/110000000/111233343/234234277'
)
select
    a.x
,   b.max_x
from temp a
    cross apply (
        select
            max(y.item.value('.[1]', 'int')) max_x
        from (select convert(xml, '<r>' + replace(a.x, '/', '</r><r>') + '</r>') xitem) x
            cross apply x.xitem.nodes('/r') as y(item)
    ) b
}}}
==== 적용 범위 ====
 * Microsoft SQL Server 2005

==== 참고 자료 ====
 * [http://www.sommarskog.se/arrays-in-sql-2005.html Arrays and Lists in SQL Server 2005]