I'm stealing my details from Here.
declare @t table(s varchar(25))
insert @t
select '122345684XT' union
select '23339034300-XT' union
select '423432424523242332X' union
select '422222222111111111232' union
select '423842389034209XYZ' union
select 'ABC'
select
left(s,patindex('%[^0-9]%',S+' ')-1 ) nbr
,right(s,len(s)-patindex('%[^0-9]%',S+' ')+1) alpha
from @t
这会导致
122345684 XT
23339034300 -XT
422222222111111111232
423432424523242332 X
423842389034209 XYZ
ABC
在您的环境中使用它。
SELECT *
FROM YourTable
ORDER BY left(s,patindex('%[^0-9]%',S+' ')-1 ),
right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)
展示者:
declare @t table(s varchar(25))
insert @t
select '12X' union
select '1X' union
select '2X' union
select '2Y' union
select '20' union
select '21'
SELECT *
FROM @t
ORDER BY CAST(left(s,patindex('%[^0-9]%',S+' ')-1 ) AS INT),
right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)
结果
1X
2X
2Y
12X
20
21
ORDER BY CASE WHEN ISNUMERIC(column)=1 THEN 1 ELSE 0 END,column
- Mihai