tsql 更改字段排序规则_tsql-如何在包含数字的SQL Server中对VARCHAR列进行排序？

tsql-如何在包含数字的SQL Server中对VARCHAR列进行排序？

我在SQL Server 2000数据库中有一个VARCHAR列，其中可以包含字母或数字。 这取决于在前端为客户配置应用程序的方式。

当它确实包含数字时，我希望对其进行数字排序，例如 为“ 1”，“ 2”，“ 10”，而不是“ 1”，“ 10”，“ 2”。 仅包含字母，字母和数字(例如“ A1”)的字段可以按字母顺序进行排序。 例如，这将是可接受的排序顺序。

1

2

10

A

B

B1

实现此目标的最佳方法是什么？

Tim C asked 2020-02-08T20:17:19Z

11个解决方案

74 votes

一种可能的解决方案是在数字值前加一个字符，以使所有字符都具有相同的字符串长度。

这是使用该方法的示例：

select MyColumn

from MyTable

order by

case IsNumeric(MyColumn)

when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn

else MyColumn

end

100应该替换为该列的实际长度。

Aleris answered 2020-02-08T20:17:35Z

12 votes

有几种可能的方法可以做到这一点。

一个是

SELECT

...

ORDER BY

CASE

WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value)

ELSE 9999999 -- or something huge

END,

value

ORDER BY的第一部分将所有内容转换为一个int(对于非数字具有巨大的价值，要排在最后)，然后最后一部分负责字母。

请注意，此查询的性能可能至少对大量数据具有中等程度的影响。

Cowan answered 2020-02-08T20:18:09Z

6 votes

SELECT *, CONVERT(int, your_column) AS your_column_int

FROM your_table

ORDER BY your_column_int

要么

SELECT *, CAST(your_column AS int) AS your_column_int

FROM your_table

ORDER BY your_column_int

我认为两者都相当便携。

JohnB answered 2020-02-08T20:18:33Z

5 votes

select

Field1, Field2...

from

Table1

order by

isnumeric(Field1) desc,

case when isnumeric(Field1) = 1 then cast(Field1 as int) else null end,

Field1

这将按照您在问题中给出的顺序返回值。

在进行所有这些转换后，性能将不会太好，因此另一种方法是在表中添加另一列，在该表中存储数据的整数副本，然后按该列的顺序进行排序，然后再对相关列进行排序。 显然，这将需要对在表中插入或更新数据的逻辑进行一些更改，以填充两列。 要么，要么在表上放置触发器，以便在插入或更新数据时填充第二列。

Luke Bennett answered 2020-02-08T20:18:58Z

4 votes

我以一种非常简单的方式解决了这一问题，将其写入“订单”部分

ORDER BY (

sr.codice +0

)

ASC

这似乎工作得很好，实际上我进行了以下排序：

16079 Customer X

016082 Customer Y

16413 Customer Z

因此，正确考虑了16082前面的0。

Orz answered 2020-02-08T20:19:27Z

4 votes

您总是可以将varchar-column转换为bigint，因为整数可能太短...

select cast([yourvarchar] as BIGINT)

但您应始终注意字母字符

where ISNUMERIC([yourvarchar] +'e0') = 1

+“ e0”来自[http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber]

这将导致您的陈述

SELECT

*

FROM

Table

ORDER BY

ISNUMERIC([yourvarchar] +'e0') DESC

, LEN([yourvarchar]) ASC

第一排序列会将数字放在最前面。第二个按长度排序，因此10将以0001开头(这是愚蠢的？！)

这导致了第二个版本：

SELECT

*

FROM

Table

ORDER BY

ISNUMERIC([yourvarchar] +'e0') DESC

, RIGHT('00000000000000000000'+[yourvarchar], 20) ASC

第二列现在正确地填充了'0'，因此自然排序以正确的顺序(正确！)放置了前导零(0,01,10,0100 ...)的整数-但是所有的alpha都将以'0'进行增强 -chars(性能)

所以第三个版本：

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

现在数字首先会填充'0'字符(当然，长度20可以增加)-可以对数字进行正确排序-而字母仅会被修剪

Bernhard answered 2020-02-08T20:20:23Z

2 votes

这似乎可行：

select your_column

from your_table

order by

case when isnumeric(your_column) = 1 then your_column else 999999999 end,

your_column

Corey Trager answered 2020-02-08T20:20:43Z

0 votes

SELECT FIELD FROM TABLE

ORDER BY

isnumeric(FIELD) desc,

CASE ISNUMERIC(test)

WHEN 1 THEN CAST(CAST(test AS MONEY) AS INT)

ELSE NULL

END,

FIELD

按照此链接，您需要转换为MONEY，然后转换为INT，以避免将“ $”订购为数字。

Matt Mitchell answered 2020-02-08T20:21:03Z

0 votes

该查询对您有帮助。 在此查询中，数据类型为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;

Nitika Chopra answered 2020-02-08T20:21:23Z

0 votes

这可能对您有帮助，当我遇到同样的问题时，我已经尝试过了。

SELECT *

FROM tab

ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);

Dennis answered 2020-02-08T20:21:47Z

-1 votes

SELECT *,

ROW_NUMBER()OVER(ORDER BY CASE WHEN ISNUMERIC (ID)=1 THEN CONVERT(NUMERIC(20,2),SUBSTRING(Id, PATINDEX('%[0-9]%', Id), LEN(Id)))END DESC)Rn ---- numerical

FROM

(

SELECT '1'Id UNION ALL

SELECT '25.20' Id UNION ALL

SELECT 'A115' Id UNION ALL

SELECT '2541' Id UNION ALL

SELECT '571.50' Id UNION ALL

SELECT '67' Id UNION ALL

SELECT 'B48' Id UNION ALL

SELECT '500' Id UNION ALL

SELECT '147.54' Id UNION ALL

SELECT 'A-100' Id

)A

ORDER BY

CASE WHEN ISNUMERIC (ID)=0 /* alphabetical sort */

THEN CASE WHEN PATINDEX('%[0-9]%', Id)=0

THEN LEFT(Id,PATINDEX('%[0-9]%',Id))

ELSE LEFT(Id,PATINDEX('%[0-9]%',Id)-1)

END

END DESC

Param Yadav answered 2020-02-08T20:22:03Z