[edit]
1 일반적인 사항 #
가끔씩 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 솔루션도 있다.
[edit]
2 솔루션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
*/
[edit]
3 솔루션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, '' + replace(rs, ';', ' ') + ' ') 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 = '' + replace(@GameSeqs, ';', ' ') + ' ';
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)
*/
[edit]
4 솔루션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
[edit]
5 또 다른 형태: 구분자로 구분된 문자열 숫자의 합계 구하기 #
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);
[edit]
6 더 좋은 성능 #
위에서 제시한 솔루션보다 더 좋은 성능을 발휘하는 솔루션은 CLR을 이용하는 것이다. 성능은 필자가 아는 솔루션 중에는 단연 최고다. 하지만 CLR 버전에 따른 차이의 극복 및 프로그램 유지보수의 문제가 있다. 자세한 사항은 아래의 첨부된 파일을 참고하기 바란다.
사용방법은 다음과 같다.
------------------
--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)
*/
![[http]](/moniwiki/imgs/http.png)