CODEWITHSUNDEEP
mysqlsortingalphanumeric
bassnoodle
bassnoodle

Reputation: 101

mysql sort order, need slightly different result

I have a varchar column that I am currently sorting by using: ORDER BY (col_name+0)

This column contains both digits and non-digits, and the result of this sorting is this:

D3
D111
M123-M124
M136
4
9
10
25
37b
132
147-149
168b
168ca

This sorting is almost perfect for our application, but with one exception: we want the items that start with letters to display after those that start with numbers. This being the ideal result:

4
9
10
25
37b
132
147-149
168b
168ca
D3
D111
M123-M124
M136

I'm hoping this can be achieved in the select statement, rather than needing to loop through everything in code again after the select. Any ideas?

Upvotes: 2

Views: 130

Answers (1)

fthiella
fthiella

Reputation: 49049

You can use this:

ORDER BY
  col_name regexp "^[^0-9]",
  case when col_name regexp "^[0-9]" then col_name + 0
       else mid(col_name, 2, length(col_name )-1) + 0 end,
  col_name

this will put rows that begins with a digit at the top. If col_name begins with a digit, I'm sorting by it's numeric value, if not I'm sorting by the numeric value of the string beginning at the second character.

Upvotes: 1

Related Questions