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