遇到个SQL Select Query的排序问题，请求高手帮助

Question

我有一个员工编号字段，字段型varchar()，里面的值有001,002,003,004,005...900,901...T001,T002,T003...以前我只要写 Order By EmployeeNo就会正常排序
001
002
...
901
...
T001
T002
...可是自从工号超过1000后问题就来了
001
002
099
1001
1002
101
102
...
998
999
T001
T002
...1000号以后的工号就排到中间去了，由于工号字段已经有以字母开头的，所以，我不能改字段属性，也不能用CAST强制类型转换成INT，请问有没有什么办法即可以保证数字排序准确，又能保留字母开头的工号也被正常排序(排在所有数字后面)？谢谢！

Answer 1

ORDER BY CAST(STUFF(EmployeeNo,1,1,'') AS INT)

Answer 2

order by stuff(col,1,patindex('%[^0-9]%',col)-1,'')*1

Answer 3

select * from tb order by case when charindex('T',col) = 0 then 1 else 2 end , cast(replace(col,'T','') as int)

Answer 4

order by case when isnumeric(EmployeeNo)=1 then 0 else 1 end,EmployeeNo

Answer 5

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-01 15:12:28
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb](COL NVARCHAR(10))
INSERT [tb]
SELECT '001' UNION ALL
SELECT '002' UNION ALL
SELECT '901' UNION ALL
SELECT '101' UNION ALL
SELECT 'T001' UNION ALL
SELECT 'TA005' UNION ALL
SELECT 'AB006'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from tb order by stuff(col,1,patindex('%[0-9]%',col)-1,'')*1
/*
COL
----------
001
T001
002
TA005
AB006
101
901(7 行受影响)
*/这样行吗

Answer 6

还有不带字母的？
ORDER BY CASE WHEN ISNUMERIC(EmployeeNo)=1 THEN 0 ELSE 1 END
,CASE WHEN ISNUMERIC(EmployeeNo)=0 THEN ORDER BY CAST(STUFF(EmployeeNo,1,1,'') AS INT) ELSE 0 END

Answer 7

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-01 15:12:28
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb](COL NVARCHAR(10))
INSERT [tb]
SELECT '001' UNION ALL
SELECT '002' UNION ALL
SELECT '901' UNION ALL
SELECT '101' UNION ALL
SELECT 'T001' UNION ALL
SELECT 'TA005' UNION ALL
SELECT 'AB006'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * from tb order by 1-isnumeric(col),stuff(col,1,patindex('%[0-9]%',col)-1,'')*1
/*
COL
----------
001
002
101
901
T001
TA005
AB006(7 行受影响)
*/还是要这样的效果

Answer 8

CREATE TABLE [tb](COL NVARCHAR(10))
INSERT [tb]
SELECT '001' UNION ALL
SELECT '002' UNION ALL
SELECT '901' UNION ALL
SELECT '900' UNION ALL
SELECT '101' UNION ALL
SELECT 'T001' UNION ALL
SELECT 'T002' UNION ALL
SELECT 'T003'
goselect * from tb order by case when charindex('T',col) = 0 then 1 else 2 end , cast(replace(col,'T','') as int)drop table tb/*
COL        
---------- 
001
002
101
900
901
T001
T002
T003（所影响的行数为 8 行）
*/

Answer 9

请无视我上面的回复 ，以本条为准
--------------------------------------------------------------------------
--  无耻地抄袭TONY哥的数据
--  Author : htl258(Tony)
--  Date   : 2010-04-01 15:12:28
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb](EmployeeNo NVARCHAR(10))
INSERT [tb]
SELECT '001' UNION ALL
SELECT '002' UNION ALL
SELECT '901' UNION ALL
SELECT '101' UNION ALL
SELECT 'T001' UNION ALL
SELECT 'TA005' UNION ALL
SELECT 'AB006'
GO
SELECT * FROM TB
ORDER BY CASE WHEN ISNUMERIC(EmployeeNo)=1 THEN NULL ELSE LEFT(EmployeeNo,PATINDEX('%[0-9]%',EmployeeNo)-1) END
,CASE WHEN ISNUMERIC(EmployeeNo)=0 
THEN CAST(STUFF(EmployeeNo,1,PATINDEX('%[0-9]%',EmployeeNo)-1,'') AS INT) 
ELSE CAST(EmployeeNo AS INT) END
/*
001
002
101
901
AB006
T001
TA005
*/

Answer 10

-->SQL查询如下:
select * 
from tb 
order by 1-isnumeric(col),
LEFT(col,patindex('%[0-9]%',col)-1),
stuff(col,1,patindex('%[0-9]%',col)-1,'')*1
/*
COL
----------
001
002
101
901
AB006
T001
TA005(7 行受影响)
*/如果有多个不同字母為前綴,这样才算完整.

Answer 11

SELECT EmployeeNo,
   LEFT(EmployeeNo,PATINDEX('%[0-9]%',EmployeeNo)-1) as A,  -- 如果EmployeeNo不带字符
   CAST(STUFF(EmployeeNo,1,PATINDEX('%[0-9]%',EmployeeNo)-1,'')AS INT) as B -- 如果EmployeeNo带字符
 FROM TB order by A,B

Answer 12

Tony, 你的源数据少了一些关键数据，数字那一部分应该再往上加，超过1000后排序就乱了，会变成下面这样...
098
099
100
1000
1001
1002
...
1009
101
1010
1011
1012
...

Answer 13

哪里会,你没测试13L那个代码吧IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb](COL NVARCHAR(10))
INSERT [tb]
SELECT '001' UNION ALL
SELECT '002' UNION ALL
SELECT '901' UNION ALL
SELECT '101' UNION ALL
SELECT 'T001' UNION ALL
SELECT 'TA005' UNION ALL
SELECT 'AB006' UNION ALL
SELECT '098' UNION ALL
SELECT '099' UNION ALL
SELECT '100' UNION ALL
SELECT '1000' UNION ALL
SELECT '1001' UNION ALL
SELECT '1002' UNION ALL
SELECT '1009' UNION ALL
SELECT '101' UNION ALL
SELECT '1010' UNION ALL
SELECT '1011' UNION ALL
SELECT '1012'
GO-->SQL查询如下:
select * 
from tb 
order by 1-isnumeric(col),
    LEFT(col,patindex('%[0-9]%',col)-1),
    stuff(col,1,patindex('%[0-9]%',col)-1,'')*1
/*
COL
----------
001
002
098
099
100
101
101
901
1000
1001
1002
1009
1010
1011
1012
AB006
T001
TA005(18 行受影响)
*/