如何订购MySQL VARCHAR结果 [英] How to Order MySQL VARCHAR Results

问题描述

在SELECT语句中，我有一个带有ORDER BY DESC的varchar列.此列中的数据示例:

In a SELECT statement, I have a varchar column with ORDER BY DESC on it. Examples of data in this column:

1234
987
12-a
13-bh

1234
987
12-a
13-bh

MySQL将返回如下所示的选择内容:

MySQL would return the select something like the following:

987
12-a
1234
13-bh

987
12-a
1234
13-bh

它将三个字符长的结果放在四个字符长的结果之前，依此类推.我希望它忽略长度，只对'-'字符前面的数字进行排序.有什么我可以订购的东西，例如IF()中的SUBSTRING，它将删除以'-'字符开头的行中的所有数据，以便我可以将CAST转换为整数?

It puts three character long results before four character long results and so on. I would like it to ignore length and just sort the numbers that come before the '-' char. Is there something that I can ORDER on like SUBSTRING in an IF() which would remove all data in a row starting with the '-' char, so that I can CAST as an integer?

推荐答案

最简单的方法是

SELECT *
FROM TBL
ORDER BY VARCHAR_COLUMN * 1;

要查看发生了什么，只需添加我用于订购的列

To see what is happening, just add the column I used for ordering

SELECT *, VARCHAR_COLUMN * 1
FROM TBL
ORDER BY VARCHAR_COLUMN * 1;

这篇关于如何订购MySQL VARCHAR结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！