在SQL中使用[a-9]而不是[0-Z]对ORDER进行ORDER

By default, SQL orders numbers before characters.

默认情况下，SQL在字符前排序数字。

So if I have the column "name":

所以，如果我有“名称”列：

abc
ab1
a1b
1ba
1bac
b21

Since SQL sorts by 0-Z (first 0 to 9, then a-Z), the query

由于SQL按0-Z（先0到9，然后是a-Z）排序，因此查询

SELECT * FROM ... ORDER BY name

will result in:

将导致：

1ba
1bac
a1b
ab1
abc
abc1
b21

But I want it to sort by a-0 (first a-Z, then 0-9).

但我希望它按a-0排序（先是a-Z，然后是0-9）。

abc
abc1
ab1
a1b
b21
1ba
1bac

How do I do this in a query? More specifically, how do I do this in SQLite?

如何在查询中执行此操作？更具体地说，我如何在SQLite中执行此操作？

I found one solution in Sort MySQL results alphabetically, but with numbers last, but only for first char.

我在按字母顺序排序MySQL结果中找到了一个解决方案，但数字最后，但仅适用于第一个字符。

5 个解决方案

#1

I would suggest SELECTing another column, say name_replace, with the digits replaced by a high-ASCII character (such as ~), then sorting on that column then on name. Unfortunately SQLite doesn't have support for regular expression replace:

我建议选择另一个列，比如name_replace，将数字替换为高位ASCII字符（例如〜），然后在该列上排序，然后在名称上排序。不幸的是，SQLite不支持正则表达式替换：

SELECT name, replace( ... replace(replace(name, '0', '~'), '1', '~') ... '9', '~') AS name_replace
  FROM mytable
 ORDER BY name_replace, name

The digits will come last when sorting on name_replace. Sorting on name will then order by the digits.

在name_replace上排序时，数字将是最后的。然后按名称排序将按数字排序。

#2

This does the trick with the data provided.

这样可以提供所提供的数据。

SELECT *
FROM Table
ORDER BY Name COLLATE SQL_EBCDIC037_CP1_CS_AS

However you may want to look into the various collation types to ensure it does what you want across the board.

但是，您可能希望查看各种排序规则类型，以确保它能够满足您的需求。

UPDATE: You mentioned SQLite however I tested this on MSSQL. Not sure if this collation is available in SQLite but comments below may have useful info.

更新：你提到了SQLite，但我在MSSQL上测试了这个。不确定SQLite中是否提供此排序规则，但下面的注释可能包含有用的信息。

#3

Test with these data please

请测试这些数据

declare @t table(a char(3))
insert @t values('ab1')
insert @t values('a1b')
insert @t values('1ba')
insert @t values('b21')
insert @t values('12a')
insert @t values('13b')


select a,
patindex('[0-9]%', a + 'a'), 
patindex('_[0-9]%', a + 'a'), 
patindex('__[0-9]%', a + 'a')
from @t order by 2, 3, 4, 1

要么

select a
from 
(select a,
patindex('[0-9]%', a + 'a') b, 
patindex('_[0-9]%', a + 'a') c, 
patindex('__[0-9]%', a + 'a') d
from @t) e
order by b, c, d, a

#4

Thsi substitutes 0-9 to above ASCII 122 (which is lower case z).

Thsi将0-9替换为ASCII 122以上（小写z）。

SQLLite doesn't have a CHAR function to do the substitution by character code (eg CHAR(123) to CHAR(132)) which may be needed instead of my CHAR(123) attempt

SQLLite没有CHAR函数来执行字符代码替换（例如CHAR（123）到CHAR（132）），这可能需要而不是我的CHAR（123）尝试

Untested of course :-)

未经测试当然:-)

ORDER BY
   REPLACE
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(name, '0', '{0')
    , '1', '{2')
    , '2', '{2')
    , '3', '{3')
    , '4', '{4')
    , '5', '{5')
    , '6', '{6')
    , '7', '{7')
    , '8', '{8')
    , '9', '{9')

Edit: although, @David Faber's solution does the same but somewhat simpler...

编辑：虽然，@ David Faber的解决方案做的相同，但有点简单......

#5

This is my idea: you add another "help_column" that checks if the first char is a number and assign 1 to it, otherwise assign 0 and then order by this column and then by name:

这是我的想法：你添加另一个“help_column”，检查第一个char是否为数字并为其分配1，否则分配0然后按此列排序，然后按名称排序：

select *, case
             when substring(name,1,1) like '[0-9]' then 1 
             else 0 
          end as help_order
from (
select 'ab1' as name union select 'a1b' as name union select '1ba' as name union select 'b21' as name
) a
order by help_order, name

Of course, you may have to improve the regular expression [0-9] to treat more than one number if necessary.

当然，如果需要，您可能必须改进正则表达式[0-9]以处理多个数字。

And, of course, you should replace the inner query (the one with several unions with your table).

当然，您应该替换内部查询（具有多个联合的查询与您的表）。

#1

SELECT name, replace( ... replace(replace(name, '0', '~'), '1', '~') ... '9', '~') AS name_replace
  FROM mytable
 ORDER BY name_replace, name