如何在 SQL Server 中对包含数字的 VARCHAR 列进行排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/119730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I sort a VARCHAR column in SQL server that contains numbers?
提问by Tim C
I have a VARCHARcolumn in a SQL Server 2000database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the customer.
我VARCHAR在SQL Server 2000数据库中有一列可以包含字母或数字。这取决于如何在前端为客户配置应用程序。
When it does contain numbers, I want it to be sorted numerically, e.g. as "1", "2", "10" instead of "1", "10", "2". Fields containing just letters, or letters and numbers (such as 'A1') can be sorted alphabetically as normal. For example, this would be an acceptable sort order.
当它确实包含数字时，我希望它按数字排序，例如“1”、“2”、“10”而不是“1”、“10”、“2”。仅包含字母或字母和数字（例如“A1”）的字段可以按字母顺序正常排序。例如，这将是可接受的排序顺序。
1
2
10
A
B
B1
What is the best way to achieve this?
实现这一目标的最佳方法是什么？
回答by Aleris
One possible solution is to pad the numeric values with a character in front so that all are of the same string length.
一种可能的解决方案是用前面的字符填充数值，以便所有数值都具有相同的字符串长度。
Here is an example using that approach:
这是使用该方法的示例：
select MyColumn
from MyTable
order by
case IsNumeric(MyColumn)
when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
else MyColumn
end
The 100should be replaced with the actual length of that column.
在100应与该列的实际长度来代替。
回答by Cowan
There are a few possible ways to do this.
有几种可能的方法可以做到这一点。
One would be
一个是
SELECT
...
ORDER BY
CASE
WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value)
ELSE 9999999 -- or something huge
END,
value
the first part of the ORDER BY converts everything to an int (with a huge value for non-numerics, to sort last) then the last part takes care of alphabetics.
ORDER BY 的第一部分将所有内容转换为 int（对于非数字具有巨大的价值，最后排序）然后最后一部分处理字母。
Note that the performance of this query is probably at least moderately ghastly on large amounts of data.
请注意，此查询的性能在大量数据上可能至少有点糟糕。
回答by JohnB
SELECT *, CONVERT(int, your_column) AS your_column_int
FROM your_table
ORDER BY your_column_int
OR
或者
SELECT *, CAST(your_column AS int) AS your_column_int
FROM your_table
ORDER BY your_column_int
Both are fairly portable I think.
我认为两者都相当便携。
回答by Luke Bennett
select
Field1, Field2...
from
Table1
order by
isnumeric(Field1) desc,
case when isnumeric(Field1) = 1 then cast(Field1 as int) else null end,
Field1
This will return values in the order you gave in your question.
这将按照您在问题中给出的顺序返回值。
Performance won't be too great with all that casting going on, so another approach is to add another column to the table in which you store an integer copy of the data and then sort by that first and then the column in question. This will obviously require some changes to the logic that inserts or updates data in the table, to populate both columns. Either that, or put a trigger on the table to populate the second column whenever data is inserted or updated.
随着所有这些转换的进行，性能不会太好，因此另一种方法是向表中添加另一列，在其中存储数据的整数副本，然后首先按该列排序，然后按相关列排序。这显然需要对在表中插入或更新数据的逻辑进行一些更改，以填充两列。要么，要么在表上放置触发器以在插入或更新数据时填充第二列。
回答by Orz
I solved it in a very simple way writing this in the "order" part
我以一种非常简单的方式解决了它，将其写在“订单”部分
ORDER BY (
sr.codice +0
)
ASC
This seems to work very well, in fact I had the following sorting:
这似乎工作得很好，实际上我有以下排序：
16079 Customer X
016082 Customer Y
16413 Customer Z
So the 0in front of 16082is considered correctly.
所以0前面的16082被认为是正确的。
回答by Bernhard
you can always convert your varchar-column to bigint as integer might be too short...
您始终可以将 varchar-column 转换为 bigint，因为整数可能太短...
select cast([yourvarchar] as BIGINT)
but you should always care for alpha characters
但你应该总是关心字母字符
where ISNUMERIC([yourvarchar] +'e0') = 1
the +'e0' comes from http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber
+'e0' 来自http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber
this would lead to your statement
这会导致你的陈述
SELECT
*
FROM
Table
ORDER BY
ISNUMERIC([yourvarchar] +'e0') DESC
, LEN([yourvarchar]) ASC
the first sorting column will put numeric on top. the second sorts by length, so 10 will preceed 0001 (which is stupid?!)
第一个排序列将把数字放在最上面。第二个按长度排序，所以 10 将在 0001 之前（这是愚蠢的？！）
this leads to the second version:
这导致了第二个版本：
SELECT
*
FROM
Table
ORDER BY
ISNUMERIC([yourvarchar] +'e0') DESC
, RIGHT('00000000000000000000'+[yourvarchar], 20) ASC
the second column now gets right padded with '0', so natural sorting puts integers with leading zeros (0,01,10,0100...) in correct order (correct!) - but all alphas would be enhanced with '0'-chars (performance)
第二列现在用“0”正确填充，因此自然排序将带前导零的整数（0,01,10,0100...）按正确顺序（正确！） - 但所有 alpha 都将用“0”增强-chars（性能）
so third version:
所以第三个版本：
SELECT
*
FROM
Table
ORDER BY
ISNUMERIC([yourvarchar] +'e0') DESC
, CASE WHEN ISNUMERIC([yourvarchar] +'e0') = 1
THEN RIGHT('00000000000000000000' + [yourvarchar], 20) ASC
ELSE LTRIM(RTRIM([yourvarchar]))
END ASC
now numbers first get padded with '0'-chars (of course, the length 20 could be enhanced) - which sorts numbers right - and alphas only get trimmed
现在数字首先用 '0'-chars 填充（当然，长度 20 可以增强） - 对数字进行正确排序 - 并且 alphas 只会被修剪
回答by Corey Trager
This seems to work:
这似乎有效：
select your_column
from your_table
order by
case when isnumeric(your_column) = 1 then your_column else 999999999 end,
your_column
回答by Matt Mitchell
回答by Nitika Chopra
This query is helpful for you. In this query, a column has data type varchar is arranged by good order.For example- In this column data are:- G1,G34,G10,G3. So, after running this query, you see the results: - G1,G10,G3,G34.
此查询对您有帮助。在此查询中，具有数据类型 varchar 的列按良好顺序排列。例如- 在此列中的数据是：- G1、G34、G10、G3。因此，运行此查询后，您会看到结果：- G1,G10,G3,G34。
SELECT *,
(CASE WHEN ISNUMERIC(column_name) = 1 THEN 0 ELSE 1 END) IsNum
FROM table_name
ORDER BY IsNum, LEN(column_name), column_name;
回答by Dennis
This may help you, I have tried this when i got the same issue.
这可能对你有帮助，当我遇到同样的问题时，我已经试过了。
SELECT *
FROM tab
ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);
SELECT *
FROM tab
ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);
