sql - ORDER/SORT 列混合数字和以字符为前缀的数字

Question

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

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

等等...

我想按以下规则对此列进行排序:

1. 仅限数字代码
2. 前缀代码，其中字母部分按字母数字顺序排列，数字部分按数字顺序排列

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

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

如何获得按此条件排序的结果集？我尝试过这样的查询:

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

但是我得到的结果是首先对数字进行排序，然后对前缀数字进行排序，但是 alpha 前缀数字的排序方式类似于 char，而不是按照我想要的方式...

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

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

我想要得到:

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

数据库是 Microsoft SQL Server。

Answer 1

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

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)

或者，第二个标准可以重写如下:

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

如果

PATINDEX('[^0-9]%', Code) 在 Code 开头找到非数字字符，则返回 1，否则返回 0 。因此，STUFF 要么删除 1 个字符，要么不删除任何字符，即与之前相同。