拆分列值心得

 

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

--  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 行受影响)

*/