字符串按顺序重组方法之自定义函数（字符串拆分重组）

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

-- Author : htl258(Tony)

-- Date : 2010-04-26 08:45:19

-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog : http://blog.csdn.net/htl258

-- Subject: 字符串按顺序重组方法之自定义函数

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

if object_id('f_GetStr') is not null

drop function f_GetStr

go

create function dbo.f_GetStr(@s varchar(100))

returns varchar(100)

as

begin

declare @x xml

select @x='<v>'+replace(@s,',','</v><v>')+'</v>'

select @s=null

select @s=isnull(@s+',','')+s

from (

select A.x.value('.','varchar(100)') as s

from @x.nodes('/v')AS A(x)

)t

order by s

return @s

end

go

declare @t table(id int ,s varchar(100))

insert @t values (1,'g,x,z,t')

insert @t values (2,'f,a,x,g')

insert @t values(3,'谁，是，你')

insert @t values(4,'你，是，谁')

insert @t values(5,'5，4，2，8，8')

select *,dbo.f_GetStr(s) as new_s from @t

/*

id s new_s

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

1 g,x,z,t g,t,x,z

2 f,a,x,g a,f,g,x

3 谁，是，你 你,是,谁

4 你，是，谁 你,是,谁

5 5，4，2，8，82,4,5,8,8

(5 行受影响)

*/