6

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?

SeanFromIT
  • 644
  • 1
  • 9
  • 18
  • `... ORDER BY CAST(SUBSTR(yourfield, 0, LOCATE('-', yourfield)) AS INTEGER)` would be a good starting point. It'll fail if there's no '-' in the field, since you'd be doing a 0-length substring. – Marc B Feb 20 '11 at 03:38

3 Answers3

16

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;
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • This is much more elegant than what I just came up with, which also works: ORDER BY IF(LOCATE('-', columnName) != 0, CAST(SUBSTRING(columnName, 1,LOCATE('-', columnName) - 1) AS DECIMAL), CAST(columnName AS DECIMAL)) DESC – SeanFromIT Feb 20 '11 at 04:16
  • +1 I love solutions like this! (Assuming it works consistently.) – Mark Eirich Feb 20 '11 at 04:27
0

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

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.

MestreLion
  • 12,698
  • 8
  • 66
  • 57
  • Nevermind about my suggestion on the REPLACE approach: since MySQL has no support for regular expressions on replaces (only for searching), its not a good approach. Go with my proposed "CONCAT -> SUBSTR -> CAST AS NUMERIC" solution. – MestreLion Feb 20 '11 at 03:56
  • you cannot cast to integer. It must be CAST to UNSIGNED. See my answer anyway – RichardTheKiwi Feb 20 '11 at 04:01
  • Youre right.. in MySQL is UNSIGNED, not INTEGER. I always mess with Oracle/MS SQL/MySQL datatypes. In MySQL, INTEGER is not a "true" datatype, its just an alias, hence you cant "CAST" it. But anyway... your solution is **sleek**!!! Does it work? I have no idea what MySQL do with this "implicit cast" – MestreLion Feb 20 '11 at 04:07
  • @richard : +1 for your answer... cant be easier and sleeker than that! Good call! – MestreLion Feb 20 '11 at 04:11
0
...
....
CAST(COL as SIGNED)  DESC
keithics
  • 8,576
  • 2
  • 48
  • 35