T-SQL ORDER BY 数字和字母混合在字符串中 [英] T-SQL ORDER BY number and letters mixed in string

问题描述

我有一系列独特的字符串，其中包含数字，有时包含数字和一个字母，其中一个示例如下:

I have a range of unique strings which contain numbers and sometimes numbers and a letter, a sample of which reads:

• 1X
• 2X
• 2 年
• 12X
• 20
• 21

数字总是在字母之前.什么是 ORDER BY (T-SQL) 子句解决方案来生成一个列表，它会给我上面演示的顺序?

The number/s always precede the letter. What is the ORDER BY (T-SQL) clause solution to produce a list which would give me the order as demonstrated above?

我尝试使用

LEN(fieldName), fieldname - 这对 20 和 21 有效.我尝试将字符串表示为整数，但 CAST 在转换中失败过程.

LEN(fieldName), fieldname - which would work but for the 20 and 21. I have tried expressing the strings as an integer but the CAST fails in the conversion process.

推荐答案

我正在从 这里.

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

在您的上下文中使用它.

To use it in your context.

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

这篇关于T-SQL ORDER BY 数字和字母混合在字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！