如何在 SQL 中对嵌入数字的字符串列进行排序？

对 SQL 中嵌入数字的字符串列进行排序

对 SQL 中包含数字的字符串列进行排序可能会带来挑战。许多数据库的自然排序算法将数字放置在错误的顺序中，例如将“a 12”放置在“a 1”之后。这种行为对于大多数应用程序来说是可以接受的，但对于特定需求来说可能是不受欢迎的。

是否可以使用纯 SQL 对嵌入数字的字符串列进行排序？

解决方案

假设该列遵循“WORD space NUMBER”的特定模式，则可以使用以下代码片段对列进行正确排序：

SELECT *
FROM table
ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED)

其工作原理如下：

• LOCATE(' ',column) 查找字符串中空格字符的位置。
• SUBSTRING(column,LOCATE(' ',column) 1) 提取字符串的数字部分
• CAST(... AS SIGNED) 将提取的数字转换为有符号整数以进行比较。

概念证明

以下示例演示了排序行为：

mysql> INSERT INTO t (st) VALUES ('a 1'),('a 12'),('a 6'),('a 11');
mysql> SELECT * FROM t ORDER BY st;
mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);

第二个查询对结果进行正确排序，将“a 1”放在“a 12”之前。

注意事项

如果列模式与“WORD space NUMBER”不同，则可能需要不同的解决方法。

增强

以下增强查询添加双重排序以打破带有数字值的字母前缀：

ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)

此修改后的代码片段提供了更全面的排序解决方案。

以上是如何在 SQL 中对嵌入数字的字符串列进行排序？的详细内容。更多信息请关注PHP中文网其他相关文章！