如何订购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屋!