---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-10 14:06:39
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([col] nvarchar(17))
Insert tb
Select 'z120' union all
Select 'z210' union all
Select 'z11' union all
select 'z21' union all
Select 'JJ11' union all
select 'JJ21' union all
Select 'JJ111' union all
select 'JJ211'
Go
--Select * from tb-->SQL查询如下:
select *
from tb
order by
left(col,patindex('%[0-9]%',col)-1),
right(col,len(col)-len(left(col,patindex('%[0-9]%',col)-1)))*1
/*
col
-----------------
JJ11
JJ21
JJ111
JJ211
z11
z21
z120
z210(8 行受影响)
*/
--> 测试数据: @s
declare @s table (a varchar(4))
insert into @s
select 'z120' union all
select 'z210' union all
select 'z11' union all
select 'z21'select * from @s order by cast(right(a,len(a)-1) as int)
--上面是针对数据中只有一个字母,如果多个字母。
--> 测试数据: @s
declare @s table (a varchar(4))
insert into @s
select 'z120' union all
select 'z210' union all
select 'z11' union all
select 'z21'select * from @s order by cast(substring(a,patindex('%[0-9]%',a),len(a)) as int)
背着灵魂漫步的回复就不行了
Drop table [tb]
Go
Create table [tb]([col] nvarchar(17))
Insert tb
Select 'z120' union all
Select 'z210' union all
Select 'z11' union all
select 'z21' union all
Select 'JJ11z' union all
select 'JJ21z' union all
Select 'JJ111z' union all
select 'JJ211zjre'
Go
--Select * from tb-->SQL查询如下:
select *
from tb
order by
left(col,patindex('%[0-9]%',col)-1),
substring(col,patindex('%[0-9]%',col),len(col)-patindex('%[0-9]%',col)-patindex('%[0-9]%',reverse(col))+2)*1
/*
col
-----------------
JJ11z
JJ21z
JJ111z
JJ211zjre
z11
z21
z120
z210(8 行受影响)
*/