Contents

1
2 襭1
3 襭2
4 所 ..
5 襭3
6 るジ : 蟲覿襦 蟲覿 覓語 螻 蟲蠍
7 覓語 split
8 譬 焔
9 るジ
10
11 谿瑚 襭


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 襭 .

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
*/

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, '<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)
*/

4 所 .. #

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

5 襭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 


6 るジ : 蟲覿襦 蟲覿 覓語 螻 蟲蠍 #

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

7 覓語 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

8 譬 焔 #

襭覲企 譬 焔レ 覦 襭 CLR 伎 蟆企. 焔レ 螳 襭 譴 豕螻. 讌襷 CLR 覯 磯ジ 谿伎 蠏豪概 覦 襦蠏碁 讌覲伎 覓語螳 . 誤 豌覿 殊 谿瑚蠍 覦.

UFN_CLR_Split.zip
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)
*/


9 るジ #

れ螻 螳 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

10 #

  • Microsoft SQL Server 2005

11 谿瑚 襭 #