日期:2014-05-17 浏览次数:20639 次
--创建示例数据
create table #TA(a varchar(500))
insert into #TA select '12,343,21,523,234,328'
insert into #TA select '12,343,23,523,234,328'
insert into #TA select '12,343,22,523,234,328'
insert into #TA select '12,343,24,523,234,328'
--解决方案,函数脚本在下面
select * from #TA order by dbo.getstrofindex(a,3,',')
/* a
------------------------
12,343,21,523,234,328
12,343,22,523,234,328
12,343,23,523,234,328
12,343,24,523,234,328
(4 行受影响)
*/
GO
-- 函数脚本
create function getstrofindex (@str varchar(8000),@index int =0,@split Nvarchar(5))
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1 --如果习惯从0开始则select @next =0
select @location = charindex(@split,@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(@split,@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出,则认为逗号在字符串后
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值1
if (@index <> @next ) select @str_return = '' --如果二者不相等,则是因为逗号太少,或者@index小于@next的初始值1。
return @str_return
end
select * from [aaa] order by split(bbb,",")(0) asc