How to Order MySQL VARCHAR Results

Question

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

MySQL would return the select something like the following:

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?

Answer 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;