如何在sql中对nvarchar进行排序 [英] How to sort a nvarchar in sql

问题描述

我有混合数据作为许可我不需要对该字段进行排序



例如：

permit_no

i have mixed data as permit no i need to sort that field

eg:
permit_no

01/13-14/ID/2HY
02/13-14/ID/2HY
03/13-14/ID/2HY
04/13-14/ID/2HY
05/13-14/ID/2HY

喜欢这个

当我尝试对结果进行排序后，在10美元之后收到100

例如：

like this
when i'am trying to sort the result is comming 100 after 10
eg:

08/13-14/ID/2HY
09/13-14/ID/2HY
10/13-14/ID/2HY
100/13-14/ID/2HY
101/13-14/ID/2HY
102/13-14/ID/2HY

推荐答案

字符串排序将始终提供此结果，因为它适用于字符旁边的字符字符基础而不是将数据视为一组数字字段。

如果要按数值对其进行排序，则必须使用 SUBSTRING [ ^ ]提取数字部分，然后CAST或CONVERT它到一个数值。这会使你的SORT BY语句变得非常复杂，所以我建议你生成一个SQL函数来提取相关的字段编号并将其作为整数返回进行排序 - 你需要 SORT BY extract（ myColumn，1），extract（myColumn，2），等来处理你的多个数值，以及最后的基于文本的代码。



祝你好运！ （我想你会需要它）

String sorting will always give this result, because it works on a character-by-character basis rather than looking at the data as a set of numeric fields.
If you want to sort it by numeric values, you will have to use SUBSTRING[^] to extract the numeric portions, and then CAST or CONVERT it to a numeric value. This is going to complicate your SORT BY statement horribly, so I would suggest that you produce an SQL function to extract the relevant field number and return it as an integer for sorting - you will need to SORT BY extract(myColumn, 1), extract(myColumn, 2), etc to cope with your multiple numeric values, as well as the text based code at the end.

Good luck! (I think you are going to need it)

访问链接

http://blog.sqlauthority.com/2008/10/14/ sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only / [ ^ ]

visit link
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/[^]

SELECT 
   permit_no 
from
   table_name
Order by
   dbo.udf_GetNumeric('permit_no');

快乐编码！

：）

Happy Coding!
:)

这篇关于如何在sql中对nvarchar进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！