拆分列值心得

/*---------------------------------

-- Author : 分拆列值htl258(Tony)

-- Date : 2009-09-10 01:38:02

-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

Mar 29 2009 10:27:29

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/

IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb (id INT,col VARCHAR(30))

INSERT INTO tb VALUES(1,'aa,bb')

INSERT INTO tb VALUES(2,'aaa,bbb,ccc')

GO

--1.2000/2005通用方法

SELECT

a.id,

col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number)

FROM tb a

JOIN master..spt_values b

ON b.type='p'

--AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可

AND CHARINDEX(',',','+a.col,number)=number

--结果:

/*

id col

----------- --------------------------------------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)

*/

--2.2005以上新方法:

SELECT a.id,b.col

FROM (

SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS XML)

FROM tb

) AS a

OUTER APPLY (

SELECT C.value('.','varchar(50)') AS col --此处value必须为小写

FROM a.col.nodes('/v') AS T(C)

) AS b

--结果:

/*

id col

----------- --------------------------------------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)

*/

--3.游标循环法:

DECLARE @t TABLE (id INT,col NVARCHAR(50))

DECLARE @id INT,@col nvarchar(200)

DECLARE c CURSOR FOR SELECT * FROM tb

OPEN c

FETCH NEXT FROM c INTO @id,@col

WHILE @@FETCH_STATUS = 0

BEGIN

WHILE CHARINDEX(',',@col)>0

BEGIN

INSERT @t SELECT @id,LEFT(@col,CHARINDEX(',',@col+',')-1)

SET @col=STUFF(@col,1,CHARINDEX(',',@col),'')

END

INSERT @t SELECT @id,LEFT(@col,CHARINDEX(',',@col+',')-1) --退出循环后插入最后获取的值

FETCH NEXT FROM c INTO @id,@col

END

CLOSE c

DEALLOCATE c

--查询

SELECT * FROM @t

--结果:

/*

id col

----------- --------------------------------------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)

*/

--4.SQL2005 函数法:

IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb (id INT,col VARCHAR(30))

INSERT INTO tb VALUES(1,'aa,bb')

INSERT INTO tb VALUES(2,'aaa,bbb,ccc')

GO

IF OBJECT_ID('f_str') IS NOT NULL

DROP FUNCTION f_str

GO

CREATE FUNCTION f_str(@str VARCHAR(20))

RETURNS @t TABLE(col VARCHAR(20))

AS

BEGIN

SET @str=@str+','

WHILE len(@str)>0

BEGIN

INSERT @t SELECT LEFT(@str,CHARINDEX(',',@str)-1)

SET @str=STUFF(@str,1,CHARINDEX(',',@str),'')

END

RETURN

END

GO

--调用查询

SELECT a.id,b.col

FROM tb a

CROSS APPLY f_str(a.col) b

/*

id col

----------- --------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)

*/

--5.SQL2005函数法二:

IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb (id INT,col VARCHAR(30))

INSERT INTO tb VALUES(1,'aa,bb')

INSERT INTO tb VALUES(2,'aaa,bbb,ccc')

GO

IF OBJECT_ID('f_str') IS NOT NULL

DROP FUNCTION f_str

GO

CREATE FUNCTION f_str(@str VARCHAR(50))

RETURNS @t TABLE(col VARCHAR(50))

AS

BEGIN

DECLARE @xml XML

SET @xml='<v>'+REPLACE(@str,',','</v><v>')+'</v>'

INSERT @t SELECT C.value('.','varchar(50)') FROM @xml.nodes('/v') AS T(C)

RETURN

END

GO

--调用查询

SELECT a.id,b.col

FROM tb a

CROSS APPLY f_str(a.col) b

/*

id col

----------- ------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)

*/