[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, '<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)
*/
[edit]
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
[edit]
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
[edit]
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);
[edit]
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
[edit]
8 譬 焔 #
襭覲企 譬 焔レ 覦 襭 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)
*/
[edit]
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
![[http]](/moniwiki/imgs/http.png)
鏤