Natural (human alpha-numeric) sort in Microsoft SQL 2005
我们有一个大型数据库，在数据库上可以进行数据库分页。这很快，只需几秒钟即可返回数百万条记录中的50行的页面。
用户可以定义自己的排序方式，基本上是选择要作为排序依据的列。列是动态的-一些具有数字值，一些日期和一些文本。
尽管大多数排序都是按预期方式进行的，但文本却以愚蠢的方式排序。好吧，我说这很愚蠢，这对计算机有意义，但会使用户感到沮丧。
例如，按字符串记录ID排序可得到类似以下内容：
1
2
3
4
5
6
7
| rec1
rec10
rec14
rec2
rec20
rec3
rec4 |
...等等。
我希望这个考虑到这个数字，所以：
1
2
3
4
5
6
7
| rec1
rec2
rec3
rec4
rec10
rec14
rec20 |
我无法控制输入(否则我只能将格式设置为前导000)，也不能依赖单一格式-有些内容例如" {alpha code}-{dept code}-{rec id}"。
我知道在C＃中执行此操作的几种方法，但是无法拉下所有记录来对它们进行排序，因为那样会很慢。
有谁知道一种在SQL Server中快速应用自然排序的方法？
我们正在使用：
1
| ROW_NUMBER() over (order by {field name} asc) |
然后我们按此进行分页。
我们可以添加触发器，尽管不能。它们的所有输入都是经过参数设置的，但我无法更改格式-如果将它们放入" rec2"和" rec10"，则它们将以自然顺序返回。
我们具有有效的用户输入，这些输入针对不同的客户端采用不同的格式。
可能会进入rec1，rec2，rec3，... rec100，rec101
还有一个可能会出现：grp1rec1，grp1rec2，... grp20rec300，grp20rec301
当我说我们无法控制输入时，我的意思是我们不能强迫用户更改这些标准-它们具有类似grp1rec1的值，而我不能将其重新格式化为grp01rec001，因为这将更改用于查找和更改的内容。链接到外部系统。
这些格式差异很大，但通常是字母和数字的混合形式。
用C＃对它们进行排序很容易-只需将其分解为{"grp", 20,"rec", 301 }，然后依次比较序列值即可。
但是，可能有数百万条记录并且分页了数据，我需要在SQL Server上进行排序。
SQL Server按值排序，而不是按比较排序-在C＃中，我可以将值拆分出来进行比较，但是在SQL中，我需要一些逻辑(非常快速地)获得一个始终排序的单个值的逻辑。
@moebius-您的答案可能有用，但是为所有这些文本值添加一个排序键确实感觉很丑陋。
1
| order by LEN(value), value |
虽然不完美，但是在很多情况下效果很好。
我看到的大多数基于SQL的解决方案都在数据变得足够复杂(例如其中一个或两个以上的数字)时中断。最初，我尝试在T-SQL中实现满足我的要求的NaturalSort函数(除其他事项外，处理字符串中任意数量的数字)，但是性能太慢了。
最终，我用C＃写了一个标量CLR函数，以实现自然排序，即使使用未经优化的代码，从SQL Server调用它的性能也非常快。具有以下特点：
-
将正确地对前1,000个字符进行排序(可以轻松地在代码中修改或制成参数)
-
正确地对小数进行排序，因此123.333在123.45之前
-
由于上述原因，可能无法正确排序IP地址之类的内容；如果您希望其他行为，请修改代码
-
支持对其中包含任意数字的字符串进行排序
-
可以正确地对长度不超过25位的数字进行排序(可以轻松地在代码中修改或制成参数)
代码在这里：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
| using System;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
public class UDF
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic=true)]
public static SqlString Naturalize(string val)
{
if (String.IsNullOrEmpty(val))
return val;
while(val.Contains(" "))
val = val.Replace(" ","");
const int maxLength = 1000;
const int padLength = 25;
bool inNumber = false;
bool isDecimal = false;
int numStart = 0;
int numLength = 0;
int length = val.Length < maxLength ? val.Length : maxLength;
//TODO: optimize this so that we exit for loop once sb.ToString() >= maxLength
var sb = new StringBuilder();
for (var i = 0; i < length; i++)
{
int charCode = (int)val[i];
if (charCode >= 48 && charCode <= 57)
{
if (!inNumber)
{
numStart = i;
numLength = 1;
inNumber = true;
continue;
}
numLength++;
continue;
}
if (inNumber)
{
sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength));
inNumber = false;
}
isDecimal = (charCode == 46);
sb.Append(val[i]);
}
if (inNumber)
sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength));
var ret = sb.ToString();
if (ret.Length > maxLength)
return ret.Substring(0, maxLength);
return ret;
}
static string PadNumber(string num, bool isDecimal, int padLength)
{
return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0');
}
} |
若要进行注册，以便可以从SQL Server调用它，请在查询分析器中运行以下命令：
1
2
3
4
5
| CREATE ASSEMBLY SqlServerClr FROM 'SqlServerClr.dll' --put the full path to DLL here
go
CREATE FUNCTION Naturalize(@val as nvarchar(max)) RETURNS nvarchar(1000)
EXTERNAL NAME SqlServerClr.UDF.Naturalize
go |
然后，您可以像这样使用它：
1
2
3
| select *
from MyTable
order by dbo.Naturalize(MyTextField) |
注意：如果在SQL Server中遇到错误，则.NET Framework中的用户代码执行将被禁用。启用" clr enabled"配置选项。，请按照此处的说明进行启用。确保这样做之前先考虑安全隐患。如果您不是数据库管理员，请确保在与服务器配置进行任何更改之前与管理员进行讨论。
注意2：此代码不正确支持国际化(例如，假定小数点标记为"。"，未针对速度进行优化等)，欢迎提出改进建议！
编辑：将函数重命名为Naturalize而不是NaturalSort，因为它没有进行任何实际排序。
我知道这是一个古老的问题，但我只是遇到了这个问题，因为它没有得到公认的答案。
我一直使用类似的方式：
1
2
| SELECT [Column] FROM [Table]
ORDER BY RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))), 1000) |
出现此问题的唯一常见时间是，如果您的列不能转换为VARCHAR(MAX)，或者LEN([Column])> 1000(但您可以将1000更改为其他值)，但是您可以根据您的需要使用这个粗略的想法。
而且，这比正常的ORDER BY [Column]的性能要差得多，但是它确实为您提供了OP中要求的结果。
编辑：只是为了进一步澄清，如果您有十进制值(例如具有1，1.15和1.5(它们将按{1, 1.5, 1.15}排序))，则以上内容将不起作用，因为这不是要求的内容在OP中，但可以通过以下方式轻松完成：
1
2
| SELECT [Column] FROM [Table]
ORDER BY REPLACE(RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))) + REPLICATE('0', 100 - CHARINDEX('.', REVERSE(LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX))))), 1)), 1000), '.', '0') |
结果：{1, 1.15, 1.5}
而且仍然完全在SQL中。这不会对IP地址进行排序，因为您现在正进入非常特定的数字组合，而不是简单的文本+数字。
RedFilter的答案对于索引大小不重要的合理大小的数据集非常有用，但是，如果要创建索引，则需要进行一些调整。
首先，将函数标记为不进行任何数据访问并且确定性和精确性：
1
2
3
| [SqlFunction(DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true, IsPrecise = true)] |
接下来，MSSQL的索引键大小限制为900字节，因此，如果归化值是索引中唯一的值，则其长度最多为450个字符。如果索引包含多个列，则返回值必须更小。两项更改：
1
2
| CREATE FUNCTION Naturalize(@str AS nvarchar(max)) RETURNS nvarchar(450)
EXTERNAL NAME ClrExtensions.Util.Naturalize |
并在C＃代码中：
1
| const int maxLength = 450; |
最后，您将需要向表中添加一个计算列，并且该列必须保留(因为MSSQL无法证明Naturalize是确定性和精确的)，这意味着归化值实际上存储在表中，但仍会自动维护：
1
| ALTER TABLE YourTable ADD nameNaturalized AS dbo.Naturalize(name) PERSISTED |
您现在可以创建索引！
1
| CREATE INDEX idx_YourTable_n ON YourTable (nameNaturalized) |
我还对RedFilter的代码进行了几处更改：使用char进行清晰说明，将重复空间的删除合并到主循环中，一旦结果超出限制就退出，设置最大长度而没有子字符串，等等。结果如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
public static class Util
{
[SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
public static SqlString Naturalize(string str)
{
if (string.IsNullOrEmpty(str))
return str;
const int maxLength = 450;
const int padLength = 15;
bool isDecimal = false;
bool wasSpace = false;
int numStart = 0;
int numLength = 0;
var sb = new StringBuilder();
for (var i = 0; i < str.Length; i++)
{
char c = str[i];
if (c >= '0' && c <= '9')
{
if (numLength == 0)
numStart = i;
numLength++;
}
else
{
if (numLength > 0)
{
sb.Append(pad(str.Substring(numStart, numLength), isDecimal, padLength));
numLength = 0;
}
if (c != ' ' || !wasSpace)
sb.Append(c);
isDecimal = c == '.';
if (sb.Length > maxLength)
break;
}
wasSpace = c == ' ';
}
if (numLength > 0)
sb.Append(pad(str.Substring(numStart, numLength), isDecimal, padLength));
if (sb.Length > maxLength)
sb.Length = maxLength;
return sb.ToString();
}
private static string pad(string num, bool isDecimal, int padLength)
{
return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0');
}
} |
这是为SQL 2000编写的解决方案。对于较新的SQL版本，可能可以对其进行改进。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
| /**
* Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
*
* @author Alexandre Potvin Latreille (plalx)
* @param {nvarchar(4000)} string The formatted string.
* @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
* @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
*
* @return {nvarchar(4000)} A string for natural sorting.
* Example of use:
*
* SELECT Name FROM TableA ORDER BY Name
* TableA (unordered) TableA (ordered)
* ------------ ------------
* ID Name ID Name
* 1. A1. 1. A1-1.
* 2. A1-1. 2. A1.
* 3. R1 --> 3. R1
* 4. R11 4. R11
* 5. R2 5. R2
*
*
* As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
* We can use this function to fix this.
*
* SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
* TableA (unordered) TableA (ordered)
* ------------ ------------
* ID Name ID Name
* 1. A1. 1. A1.
* 2. A1-1. 2. A1-1.
* 3. R1 --> 3. R1
* 4. R11 4. R2
* 5. R2 5. R11
*/
ALTER FUNCTION [dbo].[udf_NaturalSortFormat](
@string nvarchar(4000),
@numberLength int = 10,
@sameOrderChars char(50) = ''
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @sortString varchar(4000),
@numStartIndex int,
@numEndIndex int,
@padLength int,
@totalPadLength int,
@i int,
@sameOrderCharsLen int;
SELECT
@totalPadLength = 0,
@string = RTRIM(LTRIM(@string)),
@sortString = @string,
@numStartIndex = PATINDEX('%[0-9]%', @string),
@numEndIndex = 0,
@i = 1,
@sameOrderCharsLen = LEN(@sameOrderChars);
-- Replace all char that have the same order by a space.
WHILE (@i <= @sameOrderCharsLen)
BEGIN
SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' ');
SET @i = @i + 1;
END
-- Pad numbers with zeros.
WHILE (@numStartIndex <> 0)
BEGIN
SET @numStartIndex = @numStartIndex + @numEndIndex;
SET @numEndIndex = @numStartIndex;
WHILE(PATINDEX('[0-9]', SUBSTRING(@string, @numEndIndex, 1)) = 1)
BEGIN
SET @numEndIndex = @numEndIndex + 1;
END
SET @numEndIndex = @numEndIndex - 1;
SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex);
IF @padLength < 0
BEGIN
SET @padLength = 0;
END
SET @sortString = STUFF(
@sortString,
@numStartIndex + @totalPadLength,
0,
REPLICATE('0', @padLength)
);
SET @totalPadLength = @totalPadLength + @padLength;
SET @numStartIndex = PATINDEX('%[0-9]%', RIGHT(@string, LEN(@string) - @numEndIndex));
END
RETURN @sortString;
END |
我知道这有点老了，但是在寻找更好的解决方案时，我遇到了这个问题。我当前正在使用一个函数进行排序。对于我排序以混合字母数字命名的记录("第1项"，"第10项"，"第2项等")的目的，它工作得很好
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
| CREATE FUNCTION [dbo].[fnMixSort]
(
@ColValue NVARCHAR(255)
)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @p1 NVARCHAR(255),
@p2 NVARCHAR(255),
@p3 NVARCHAR(255),
@p4 NVARCHAR(255),
@Index TINYINT
IF @ColValue LIKE '[a-z]%'
SELECT @Index = PATINDEX('%[0-9]%', @ColValue),
@p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
@ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
ELSE
SELECT @p1 = REPLICATE(' ', 255)
SELECT @Index = PATINDEX('%[^0-9]%', @ColValue)
IF @Index = 0
SELECT @p2 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255),
@ColValue = ''
ELSE
SELECT @p2 = RIGHT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
@ColValue = SUBSTRING(@ColValue, @Index, 255)
SELECT @Index = PATINDEX('%[0-9,a-z]%', @ColValue)
IF @Index = 0
SELECT @p3 = REPLICATE(' ', 255)
ELSE
SELECT @p3 = LEFT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
@ColValue = SUBSTRING(@ColValue, @Index, 255)
IF PATINDEX('%[^0-9]%', @ColValue) = 0
SELECT @p4 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255)
ELSE
SELECT @p4 = LEFT(@ColValue + REPLICATE(' ', 255), 255)
RETURN @p1 + @p2 + @p3 + @p4
END |
然后打电话
1
| select item_name from my_table order by fnMixSort(item_name) |
对于简单的数据读取，它很容易将处理时间增加三倍，因此它可能不是理想的解决方案。
这是我喜欢的其他解决方案：
http://www.dreamchain.com/sql-and-alpha-numeric-sort-order/
这不是Microsoft SQL，但是由于我在寻找Postgres解决方案时来到这里，因此我认为在此处添加此内容将对其他人有所帮助。
对于以下varchar数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| BR1
BR2
External Location
IR1
IR2
IR3
IR4
IR5
IR6
IR7
IR8
IR9
IR10
IR11
IR12
IR13
IR14
IR16
IR17
IR15
VCR |
这对我来说最有效：
1
| ORDER BY substring(fieldName, 1, 1), LEN(fieldName) |
如果您无法从数据库中加载数据以进行C＃排序，那么我敢肯定您会以任何方式在数据库中以编程方式进行处理而感到失望。当服务器要排序时，必须像每次一样计算"感知"顺序。
我建议您在首次插入数据时使用某种C＃方法添加一个附加列来存储预处理的可排序字符串。例如，您可能尝试将数字转换为固定宽度范围，因此" xyz1"将变成" xyz00000001"。然后，您可以使用普通的SQL Server排序。
冒着冒出自己的号角的风险，我写了一篇CodeProject文章来实现CodingHorror文章中提出的问题。随时从我的代码中窃取。
只需您排序
1
2
3
4
| ORDER BY
cast (substring(name,(PATINDEX('%[0-9]%',name)),len(name))as int)
## |
我刚刚在某个地方读过一篇有关该主题的文章。关键点是：您只需要整数值即可对数据进行排序，而" rec"字符串则属于UI。您可以将信息分为两个字段，例如alpha和num，分别按alpha和num排序，然后显示一个由alpha + num组成的字符串。您可以使用计算列来组成字符串或视图。
希望能帮助到你
您可以使用以下代码解决问题：
1
2
3
4
5
6
| Select *,
substring(Cote,1,len(Cote) - Len(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1)))alpha,
CAST(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1) AS INT)intv
FROM Documents
left outer join Sites ON Sites.IDSite = Documents.IDSite
Order BY alpha, intv |
问候，
rabihkahaleh@hotmail.com
我还是听不懂(可能是因为我的英语不好)。
您可以尝试：
1
| ROW_NUMBER() OVER (ORDER BY dbo.human_sort(field_name) ASC) |
但这对数百万条记录无效。
这就是为什么我建议使用触发器将人的价值填充到单独的列中的原因。
此外：
-
内置的T-SQL函数确实
速度慢，Microsoft建议使用
.NET代替。
-
人类价值是恒定的，因此每次都没有必要计算
查询运行时。
|
