Sql订单字符串与数字asc

Question

我的数据库中有这样的字符串:

DE-1016-860
DE-1016-1078
DE-1016-1166

我如何在这样的SELECT中ORDER BY order_numbers这些元素:

DE-1016-1166
DE-1016-1078
DE-1016-860

Answer 1

找到解决方案:

ORDER BY SUBSTR(order_number FROM 1 FOR 8), CAST(SUBSTR(order_number FROM 8) AS UNSIGNED)

首先，我给它们赋值1作为起点，8作为终点:

12345678
DE-1016-

你可以看到八个字符。其次，我从第8个数字开始，它工作得很好。它给了我我的数字排序，从顶部的最大值到最后的最小值。

Answer 2

如果您的格式始终是AA-####-.........然后您可以尝试使用LEFT(), RIGHT()和SUBSTRING()。

例如:

ORDER BY LEFT(order_number,2), SUBSTRING(order_number,4,4) DESC, SUBSTRING(order_number, 9,4) DESC

Answer 3

这假设您的前缀总是相同的，因此to_number最后的字符:

  SELECT str
    FROM (SELECT 'DE-1016-860' str FROM DUAL
          UNION ALL
          SELECT 'DE-1016-1078' str FROM DUAL
          UNION ALL
          SELECT 'DE-1016-1166' str FROM DUAL)
ORDER BY TO_NUMBER (SUBSTR (str, 9, 4)) DESC