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

-- 好久没写blog，今天就看了小梁的blog,发现这小子挺用功，有"感"而发，写一个函数来分享<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

-- 这个有感于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很有意思呀！！