本文介绍了通过数字部分对字母数字字符串进行排序的SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个MySQL表,其中的VARCHAR列包含如下值:
I have a MySQL tablewith a VARCHAR column containing values as follows:
abc-1
abc-2
abc-10
...etc.
在MySQL中是否可以按数字部分对其进行排序:
Is there any way to sort it in MySQL by the numeric part in the order:
abc-1
abc-2
代替
abc-1
abc-10
abc-2
?
数据值澄清
典型值为mir-1,mir-14,mir-193,最高值为mir-4987.为简化起见,我们假设所有内容都以"mir-"开头(我可以手动处理一些例外情况).
Typical values are mir-1, mir-14, mir-193, with the highest being mir-4987. To simplify things, let's assume all begin with ‘mir-’ (I can deal with the few exceptions by hand).
推荐答案
假定您的列可以包含abc-1
或其他类似这样的值,例如defg-12
并且您要先订购abc
,然后再订购其他.
Assuming that your column can contain abc-1
or other values too like these only such as defg-12
and you want to order abc
ones first and the others ones after.
SELECT
*
FROM
your_table
ORDER BY SUBSTRING_INDEX(t, '-', 1) , CAST(SUBSTRING_INDEX(t, '-', - 1) AS UNSIGNED);
这篇关于通过数字部分对字母数字字符串进行排序的SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!