select * from pricelist order by right(Number,3) desc
declare @t table ( col varchar(20) )insert @t select 'DP-CB-111' union all select 'DP-CB4drgdfg-14' select * from @t order by right(col,charindex('-',reverse(col))-1)/** DP-CB-111 DP-CB4drgdfg-14 **/也许这是你要的效果
select * from pricelist order by right(Number,3) desc 编号最后的数字不一定是3位的,而且这样取出来是字符型,那么1开头的就排在前面了,而不是根据数值大小排列。 编号可能是dp-cb-1到 dp-cb-180
所要达到的排列顺序是 dp-cb-180 dp-cb-179 ...... dp-cb-1
declare @t table ( col varchar(20) )insert @t select 'DP-CB-1' union all select 'DP-CB-179' union all select 'DP-CB-180' select * from @t order by convert(int,right(col,charindex('-',reverse(col))-1)) desc/** DP-CB-180 DP-CB-179 DP-CB-1 **/
非常感谢,但是这样拿出来值还是根据字符在排列 select * from pricelist where Number like '%ta%' order by right(Number,charindex('-',reverse(Number))-1)desc 结果是 DP-CB-99 .... DP-CB-90 DP-CB-9 DP-CB-89 ...... DP-CB-8 DP-CB-79
你自己把cast ... as int打掉了,还说用的就是?
懒得打cast或者convert,就在后面*1 select * from pricelist where Number like '%ta%' order by 1*right(Number,charindex('-',reverse(Number))-1) desc
---先写一个取字符串中数字的函数create function dbo.F_Get_Number (@S varchar(100)) returns int AS begin while PATINDEX('%[^0-9]%',@S)>0 begin set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') end return cast(@S as int) end--测试 select DBO.F_Get_Number('测试AB03C123A0B5C')--返回/* 312305 */--你的语句可以用下面的方式实现select * from pricelist order by dbo.F_Get_Number(Number) desc
select * from pricelist order by right(Number,3) desc
declare @t table
(
col varchar(20)
)insert @t select 'DP-CB-111'
union all select 'DP-CB4drgdfg-14'
select
*
from @t
order by right(col,charindex('-',reverse(col))-1)/**
DP-CB-111
DP-CB4drgdfg-14
**/也许这是你要的效果
编号最后的数字不一定是3位的,而且这样取出来是字符型,那么1开头的就排在前面了,而不是根据数值大小排列。
编号可能是dp-cb-1到 dp-cb-180
dp-cb-180
dp-cb-179
......
dp-cb-1
(
col varchar(20)
)insert @t select 'DP-CB-1'
union all select 'DP-CB-179'
union all select 'DP-CB-180'
select
*
from @t
order by convert(int,right(col,charindex('-',reverse(col))-1)) desc/**
DP-CB-180
DP-CB-179
DP-CB-1
**/
select * from pricelist where Number like '%ta%' order by right(Number,charindex('-',reverse(Number))-1)desc
结果是
DP-CB-99
....
DP-CB-90
DP-CB-9
DP-CB-89
......
DP-CB-8
DP-CB-79
你自己把cast ... as int打掉了,还说用的就是?
select * from pricelist where Number like '%ta%' order by 1*right(Number,charindex('-',reverse(Number))-1) desc
returns int
AS
begin
while PATINDEX('%[^0-9]%',@S)>0
begin
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
end
return cast(@S as int)
end--测试
select DBO.F_Get_Number('测试AB03C123A0B5C')--返回/*
312305
*/--你的语句可以用下面的方式实现select * from pricelist order by dbo.F_Get_Number(Number) desc