T-Sql - 按字母数字排序 [英] T-Sql - Order By on Alphanumeric

问题描述

我有一个字母数字标记列表，比如 '1a', '1b', '02', '03', '10', '11', 等等...

i have a list of alphanumeric tokens, say '1a', '1b', '02', '03', '10', '11', etc...

现在，在此令牌列表上进行排序的最佳方法是什么?

Now, what's the best way to do an order by on this list of tokens?

我得到 '1a', '1b', '10', '11', '02', '03',

但我需要它

'1a', '1b', '02', '03', '10', '11' 

更新

好的，我按照建议执行此操作，但它不起作用.

ok, i am doing this after the suggestion but it's not working.

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
select '1b'
select '02'
select '10'

select * from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

我得到的响应为 '1b', '02', '10', '1a'

更新2

进行以下更改后即可使用.

It works after making the following change.

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
insert into @tokens
select '1b'
insert into @tokens
select '02'
insert into @tokens
select '10'


select token from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

感谢大家的好主意.

推荐答案

最简单的解决方案是前置零

The easiest solution is to pre-pend zeros

Select ...
From Table
Order By Right( '0000000000' + YourColumn, 10)

但是，这不会考虑字母字符.为了处理字母字符，您需要知道可能有多少潜在的字母字符.如果有，您可以执行以下操作:

However, this will not account for alpha characters. In order to deal with alpha characters, then you'd need to know how many potential alpha characters you might have. If there is one, you could do something like:

Select ...
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

添加

试运行:

If object_id('tempdb..#Test') is not null
    Drop Table #Test

Create Table #Test ( NumVal varchar(10) )
Insert #Test(NumVal) Values('02')
Insert #Test(NumVal) Values('03')
Insert #Test(NumVal) Values('1a')
Insert #Test(NumVal) Values('1b')
Insert #Test(NumVal) Values('10')
Insert #Test(NumVal) Values('11')

Select NumVal
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

Results:
1a
1b
02
03
10
11

关于我的解决方案的说明.如果字母字符具有特殊含义，那么正如 Erick Robertson 建议的那样，您应该将数据分解为单独的列.上面的解决方案只会处理两种非常特殊的情况:一个全数字值，一个带有单个尾随字母字符的值.如果数据可能有多个字母字符或字母字符有时位于值末尾以外的位置，我的解决方案将不起作用.此外，应该注意的是，我的解决方案将导致表扫描来评估每个值上的可排序字符串.

A note about my solution. If it is the case that the alphabetic character(s) has special meaning, then as Erick Robertson suggested, you should break up the data into separate columns. The above solution will only handle two very specific cases: an all numeric value, a value with a single trailing alphabetic character. If the data might have multiple alphabetic characters or the alphabetic character is sometimes positioned other than the end of the value, my solution will not work. In addition, it should be noted that my solution will cause a table scan to evaluate the order-able string on each value.

如果您寻求的是一次性快速解决方案，那么我的方法会奏效.如果您正在寻求长期解决方案，那么要么将数据分解为单独的列，接受愚蠢的排序顺序，要么添加一列来规定每个值的相对排序顺序.

If what you seek is a one-time quick solution, then my approach will work. If you are seeking a long term solution, then either break up the data into separate columns, accept the goofy sort order or add a column that dictates the relative sort order for each value.

这篇关于T-Sql - 按字母数字排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！