CODEWITHSUNDEEP
mysqlsqlsql-order-by
Legionar
Legionar

Reputation: 7607

Order string in numerical order

Lets say, I have a table, and I want to order by field with some string and size. When I use ORDER BY size, it gets something like this:

size 100
size 105
size 110
size 115
size 85
size 90
size 95

String can be different in each row, this is just example, when there is same string - size. I want to return this:

size 85
size 90
size 95
size 100
size 105
size 110
size 115

I know, that ordering by numeric string can be done with ORDER BY CAST(size AS UNSIGNED), but how to do this, when its not numeric string, but the field is string with number?

Upvotes: 1

Views: 67

Answers (2)

SMA
SMA

Reputation: 37103

You are partially right. You could just fetch the number from your alpha numeric column

SELECT   *
FROM     MyTable
ORDER BY CAST(SUBSTRING(size, LOCATE(' ',size)+1) AS SIGNED)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I simple way to do this is to use the length as a key:

order by length(size), size

Alternatively, use substring_index():

order by substring_index(size, ' ', 1),
         substring_index(size, ' ', -1) + 0

The + 0 does silent conversion to convert the second value to a number.

Upvotes: 3

Related Questions