CODEWITHSUNDEEP
sqlmysql
Dawid Ohia
Dawid Ohia

Reputation: 16465

How to use ORDER BY with proper handling of numbers?

I want to get data from mysql table sorted by one of it's varchar column. So let's say I have query like this:

SELECT name, model FROM vehicle ORDER BY model

The problem is, that for 'model' values like these: 'S 43', 'S 111' the order will be:

S 111
S 43

because I suppose ORDER BY uses alphabetic order rules, right? So how to modify this query to get "numerical" order? In which 'S 43' would be before 'S 111'? Without changing or adding any data to this table.

Upvotes: 4

Views: 134

Answers (3)

Sejanus
Sejanus

Reputation: 3323

You can take numeric part only (substring functions) and convert it into int (cast functions).

mySQL cast functions

mySQL string functions

I didn't test it myself but I suppose it should work.

Upvotes: 1

Frank Shearar
Frank Shearar

Reputation: 17142

If the non-numeric portion's of constant length, you could

ORDER BY substring(model, <length of non-numeric portion>)

or, if the non-numeric portion's length varies, you could

ORDER BY substring(model, 1 + LOCATE(' ', model))

Upvotes: 1

Aleksey Shein
Aleksey Shein

Reputation: 7482

Something like this: 

SELECT name, model 
FROM vehicle 
ORDER BY CAST(TRIM(LEADING 'S ' FROM model) AS INTEGER)

Note, that it's not a good practice to sort by function result, because it produces dynamic unindexed result which can be very slow, especially on large datasets.

Upvotes: 5

Related Questions