---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(7)) insert [tb] select '1#2#' union all select '1#11#' union all select '1#3#' union all select '1#13#' union all select '1#11#4#'
---查询--- select * from [tb] order by cast(reverse(Parsename(replace(reverse(col),'#','.'),1)) as int), cast(reverse(Parsename(replace(reverse(col),'#','.'),2)) as int), cast(reverse(Parsename(replace(reverse(col),'#','.'),3)) as int)---结果--- col ------- 1#2# 1#3# 1#11# 1#11#4# 1#13#(5 行受影响)
order by cast(replace(col1,'#','') as int)
还可以参考以下函数 /** --功能:分割字符串,取第@i个值 --Author:josy(百年树人) --参数@s:字符串 --参数@i:取第几个值 --参数@sign:分隔符 **/ create function [dbo].[f_col](@s varchar(100),@i int,@sign varchar(10)) returns varchar(200) as begin declare @t table(id int identity(1,1),col varchar(100)); declare @cnt int,@rel varchar(200) set @s=@s+@sign set @cnt=datalength(@s)-datalength(replace(@s,@sign,'')) while @cnt>0 begin insert @t(col) select left(@s,charindex(@sign,@s)-1) set @cnt=@cnt-1 set @s=stuff(@s,1,charindex(@sign,@s),'') end select @rel=col from @t where id=@i return @rel end go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(7))
insert [tb]
select '1#2#' union all
select '1#11#' union all
select '1#3#' union all
select '1#13#' union all
select '1#11#4#'
---查询---
select * from [tb]
order by
cast(reverse(Parsename(replace(reverse(col),'#','.'),1)) as int),
cast(reverse(Parsename(replace(reverse(col),'#','.'),2)) as int),
cast(reverse(Parsename(replace(reverse(col),'#','.'),3)) as int)---结果---
col
-------
1#2#
1#3#
1#11#
1#11#4#
1#13#(5 行受影响)
/**
--功能:分割字符串,取第@i个值
--Author:josy(百年树人)
--参数@s:字符串
--参数@i:取第几个值
--参数@sign:分隔符
**/
create function [dbo].[f_col](@s varchar(100),@i int,@sign varchar(10))
returns varchar(200)
as
begin
declare @t table(id int identity(1,1),col varchar(100));
declare @cnt int,@rel varchar(200)
set @s=@s+@sign
set @cnt=datalength(@s)-datalength(replace(@s,@sign,''))
while @cnt>0
begin
insert @t(col) select left(@s,charindex(@sign,@s)-1)
set @cnt=@cnt-1
set @s=stuff(@s,1,charindex(@sign,@s),'')
end
select @rel=col from @t where id=@i
return @rel
end
go