MySQL sort field by two criteria

Question

Due to very strange conventions on the hosting side, I have API fetch data that is ordered like this:

[car0]
[car0]
[car1]
[car2]
[car]

if i do sql query on my database and order by name asc

I get

[car]
[car0]
[car0]
[car1]
[car2]

I need the columns in the same order.

Is there a way to sort the string length first and then sort by asc name, so the data retrieved from mysql might look like data from the url:

+3

Edward 03 Aug At 3:09 am

1 answer

Mukesh Kalgude · Accepted Answer · 2015-08-03T05:11:01+0000

Try this way

order by CASE SUBSTRING(name, 4)
  WHEN '' THEN -1
  else SUBSTRING(name, 4) end asc