ORDER/SORT 列混合数字和以字符为前缀的数字 [英] ORDER/SORT Column mixed number and number prefixed by char

问题描述

我有一个包含代码的 varchar 列，此代码只能由数字或以字符为前缀的数字，例如我有一列包含此数据:

I have a varchar column conteining a code, this code could by only numbers, or number prefixed by a char for example i have a column containing this data :

+------+
| Code |
+------+
|  1   |
|  C1  |
|  2   |
|  3   |
|  C3  |
|  F3  |
|  F1  |
|  F17 |
|  C9  |
|  C10 |
|  C47 |
| C100 |
| C134 |
| A234 |
|C1245 |
|   10 |
|  100 |
+------+

等等......

我想按照这个规则对这个列进行排序:

I want to sort this column by this rules :

1. 只有数字代码
2. 字母部分按字母顺序排列的前缀代码，数字部分按数字顺序排列

我想实现这样排序的结果集:

I want to achieve a resultset ordered like this :

+------+
| Code |
+------+
|  1   |
|  2   |
|  3   |
| 10   |
| 100  |
| A234 |
|  C1  |
|  C3  |
|  C9  |
|  C10 |
|  C47 |
| C100 |
| C134 |
|C1245 |
|  F1  |
|  F3  |
|  F17 |
+------+

我怎样才能得到按照这个条件排序的结果集?我试过这样的查询:

How can i get a resultset ordered with this criteria ? I've tried with a query like this :

SELECT Code FROM Code_List ORDER BY case when Code like '%[a-z]%' then 99999999999999999999999999999999 else convert(decimal, Code) end

但我得到的结果是先排序数字，然后排序前缀数字，但 alpha 前缀数字的排序方式与字符类似，而不是我想要的方式...

But i get a result that order first the number and then the prefixed number but the alpha prefixed number is ordered like char and not in the manner i want it...

唯一的数字记录应该按照数字顺序而不是字符顺序进行排序，所以如果唯一的数字记录是:

The only numeric record should be ordered following the rules of the numeric order and no the character order so if the only numeric record are :

+------+
| Code |
+------+
|  1   |
|  47  |
|  2   |
|  3   |
|  6   |
|  100 |
|  112 |
|  10  |

我想得到:

+------+
| Code |
+------+
|  1   |
|  2   |
|  3   |
|  6   |
|  10  |
|  47  |
|  100 |
|  112 |

数据库是 Microsoft SQL Server.

The Database is Microsoft SQL Server.

推荐答案

假设值前没有空格且只能有 1 个字符的前缀:

Assuming there's no spaces before the values and there can only be 1-char prefix:

ORDER BY
  CASE WHEN LEFT(Code, 1) BETWEEN '0' AND '9' THEN ' ' ELSE LEFT(Code, 1) END,
  CAST(STUFF(Code, 1, CASE WHEN LEFT(Code, 1) BETWEEN '0' AND '9' THEN 0 ELSE 1 END, '') AS int)

或者，可以像这样重写第二个标准:

Alternatively, the second criterion could be rewritten like this:

  CAST(STUFF(Code, 1, PATINDEX('[^0-9]%', Code), '') AS int)

PATINDEX('[^0-9]%', Code) 如果在 Code 的开头找到一个非数字字符返回 1，否则返回 0.结果，STUFF 要么删除 1 个字符，要么不删除，即与之前相同.

PATINDEX('[^0-9]%', Code) returns 1 if it finds a non-numeric character at the beginning of Code, and 0 otherwise. As a result, STUFF either removes 1 character, or none, i.e. same as previously.

这篇关于ORDER/SORT 列混合数字和以字符为前缀的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！