如何订购MySQL VARCHAR结果

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

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

1234
987
12-a
13-bh

1234 987 12-a 13-bh

MySQL would return the select something like the following:

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

987
12-a
1234
13-bh

987 12-a 1234 13-bh

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?

它在四个字符长的结果之前放置三个字符长的结果,依此类推。我希望它忽略长度,只是对' - 'char之前的数字进行排序。有没有什么东西可以在IF()中像SUBSTRING一样进行ORDER,它会删除以' - '字符开头的行中的所有数据,这样我可以将CAST作为整数?

3 个解决方案

#1

The easiest thing to do is this

最容易做到的就是这个

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;

#2

The trick part is dealing about the "-": since its optional, you cant directly use SUBSTR in that field (as Marc B pointed out) to get rid of everything after it

诀窍部分是处理“ - ”:因为它是可选的,你不能直接在该字段中使用SUBSTR(正如Marc B指出的那样)去除它之后的所有内容

So, the trick would be: append an "-" to the value!

所以,诀窍是:在值上附加“ - ”!

Like this:

ORDER BY CAST(SUBSTR(CONCAT(yourfield,'-'), 0, LOCATE('-', CONCAT(yourfield,'-'))) AS UNSIGNED)

Another useful approach is to, instead of using SUBSTR to "remove" everything after the "-", replace it (and all letters) to "0", and THEN use CAST.

另一个有用的方法是,而不是使用SUBSTR“删除”“ - ”之后的所有内容,将它(和所有字母)替换为“0”,然后使用CAST。

#3

...
....
CAST(COL as SIGNED)  DESC

#1