SQL - ORDER a mix of numbers and words

Question

I have an SQL database with I'm calling through a php file. The column can contain both numbers and words. For example it could countain:

1
4
clowns
12
46

Naturally, ORDER BY returns:

1
12
4
46
clowns

but I would like it to sort how it would sort integers with the numbers:

1
4
12
46
clowns

Is it possible to do this?

See http://stackoverflow.com/questions/153633/natural-sort-in-mysql — bruchowski, May 09 '15 at 02:45
Depends on the dbms used. Some dbms products have collations with numeric sorting. — jarlh, May 09 '15 at 08:24

score 3 · Answer 1 · edited May 23 '17 at 10:24

add some new columns to solve this, first order by whether or not it is a number, then by the number value, and finally alphabetically by the word value.

In MSSQL you have access to the isnumeric function.
In ORACLE you could create a custom function using THIS
In MYSQL you could create a custom function using THIS

select your_column from(
select *, 
isnumeric(your_column) as ISNUMBER,
CASE WHEN ISNUMERIC(your_column) = 1 THEN CAST(your_column AS INT) ELSE null END as NUMBERVALUE,
case when isnumeric(your_column) = 1 then null else your_column end as TEXTVALUE )
order by isnumber desc, numbervalue asc, textvalue asc
...

Please specify which dbms this works for. (ISNUMERIC is a product specific function.) — jarlh, May 09 '15 at 08:25

SQL - ORDER a mix of numbers and words

1 Answers1