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 :
- 只有数字代码
- 字母部分按字母顺序排列的前缀代码,数字部分按数字顺序排列
我想实现这样排序的结果集:
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屋!