SQL order varchar as decimals

Question

I have a row that has both numbers ant strings. I whant to order it so that numbers will be ordered as numbers and all the strings would go to the end of table.

ORDER BY (
          CASE 
               WHEN `{$table}`.`{$row}` LIKE '%[^0-9]%' 
                    THEN CAST(`{$table}`.`{$row}` AS DECIMAL) 
               ELSE `{$table}`.`{$row}` 
          END
         ) ASC"

But instead the numbers are still sorted like strings.

Results: 
0
410
680
72
Some other string
Some string


It should be:
0
72
410
680
Some other string
Some string

Are you sure you are using MySQL? Your `like` expression (for the intention that you have) is supported by SQL Server but not MySQL. — Gordon Linoff, Feb 13 '14 at 16:31
@GordonLinoff is there a way to check if string is a number with mysql? — Ben, Feb 13 '14 at 16:36
Yes, although you don't need that in this case because of the silent conversion (unless you want something like `'1abc'` to go with the strings. Check this out: http://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql. — Gordon Linoff, Feb 13 '14 at 16:45

Gordon Linoff · Accepted Answer · 2014-02-13T16:57:22.403

1

Try this:

order by (case when left(`{$table}`.`{$row}`, 1) between '0' and '9' then 0 else 1 end),
         `{$table}`.`{$row}` + 0,
         `{$table}`.`{$row}`

The first expression puts numbers first (or at least strings that start with a number). The second is a nice MySQL feature that simply converts a string to a number. The third sorts the non-numeric strings.

EDIT:

To have only numbers (instead of leading numbers) go first:

order by (case when left(`{$table}`.`{$row}`, 1) REGEXP '^-?[0-9]+$' then 0 else 1 end),
         `{$table}`.`{$row}` + 0,
         `{$table}`.`{$row}`

edited Feb 13 '14 at 16:57

answered Feb 13 '14 at 16:29

Gordon Linoff

1,198,228
53
572
709

And what if i whanted the whole thing opposite? [string 10 9 4 0] – Ben Feb 13 '14 at 16:33
1

@Benedictus . . . You would add a `desc` to the first condition. – Gordon Linoff Feb 13 '14 at 16:34
in this query 12abc will be counted as integer, right? I'd rather count it as a string, how can I do that? – Ben Feb 13 '14 at 16:53

score 0 · Answer 2 · answered Feb 13 '14 at 16:44

0

How about the following (SQL Fiddle):

SELECT * FROM 
(
  SELECT field1
  FROM MyTable
  WHERE field1 REGEXP '^-?[0-9]+$'
  ORDER BY CAST(field1 AS DECIMAL)
  )AS m
UNION
SELECT * FROM 
(
  SELECT field1
  FROM MyTable
  WHERE field1 NOT REGEXP '^-?[0-9]+$'
  ORDER BY field1
) AS mm

answered Feb 13 '14 at 16:44

Linger

14,686
23
50
76

. . `union all` is not guaranteed to retain ordering. `union` definitely will not. – Gordon Linoff Feb 13 '14 at 16:46

SQL order varchar as decimals

2 Answers2