通过数字部分对字母数字字符串进行排序的SQL [英] SQL for ordering an alphanumeric string by the numeric part

问题描述

我有一个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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！