利用xml在指定分隔符的情况下获取指定数据项的指定位置的串

--  好久没写blog，今天就看了小梁的blog,发现这小子挺用功，有"感"而发，写一个函数来分享

--  这个有感于xml和以前在2000下的指定分隔符的情况下获取指定数据项的指定位置的串

 

 

--  运行环境：

 

SELECT @@version

 

/*

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

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

    Apr 14 2006 01:12:25

    Copyright (c) 1988-2005 Microsoft Corporation

    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

 

 

(1 行受影响)

*/

 

 

--  加一个分隔函数:                                                                

 

create  function F_split(

                @s varchar(8000),          --包含多个数据项的字符串

                @pos int,                 --要获取的数据项的位置

                @split varchar(10)        --数据分隔符

)RETURNS varchar(100)

AS

BEGIN

    IF @s IS NULL RETURN(NULL)

    DECLARE @splitlen int                --分隔符长度

    SELECT @splitlen=LEN(@split+'a')-2

    WHILE @pos>1 AND charindex(@split,@s+@split)>0

        SELECT @pos=@pos-1,

            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')

    RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),''))

END

GO

　　

--  数据准备

 

DECLARE @t TABLE(data varchar(50))

INSERT @t VALUES('10/20/30/50/40/90')

INSERT @t VALUES('1/2/3/5/4/9')

INSERT @t VALUES('10/5/60/33/12/1')

INSERT @t VALUES('10/8/3/60/55/20')

select dbo.F_split(data,3,'/') as col

from @t

 

/*

 

col

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

30

3

60

3

 

(4 行受影响)

*/

 

drop function F_split;

GO

 

 

--  下面我们来试试用xml的语法完成上面的功能。

 

create  function F_split(

                @s varchar(8000),          --包含多个数据项的字符串

                @pos int,                 --要获取的数据项的位置

                @split varchar(10)        --数据分隔符

)RETURNS VARCHAR(100)

AS

BEGIN

    DECLARE @RET VARCHAR(100)

    SELECT @RET =

    CAST(('<root><v>'+ REPLACE(@s,@split,'</v><v>')+'</v></root>'

         ) AS xml).

    query('//v[position()=sql:variable("@pos")]').

    value('.[1]','VARCHAR(max)')

    return @ret

END

GO

 

--  数据准备

 

DECLARE @t TABLE(data varchar(50))

INSERT @t VALUES('10/20/30/50/40/90')

INSERT @t VALUES('1/2/3/5/4/9')

INSERT @t VALUES('10/5/60/33/12/1')

INSERT @t VALUES('10/8/3/60/55/20')

select dbo.F_split(data,3,'/') as col

from @t

 

DROP FUNcTION F_split

 

/*

col

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

30

3

60

3

 

(4 行受影响)

*/

 

--  对比两个函数，大家看看是不是XML很有意思呀！！