CODEWITHSUNDEEP
mysqlsqlsql-order-by
flpost
flpost

Reputation: 91

order by a certain character

let's suppose we have a certain table containing a 'name' column with the following rows:

'A'
'K'
'_C'
'_B'

is there a way to order it alphabetically and placing the names that come with an underscore '_' first (alphabetically too) using ORDER BY on MySQL? so the output would be:

'_B'
'_C'
'A'
'K'

Upvotes: 1

Views: 120

Answers (1)

davek
davek

Reputation: 22925

order by 
       case when substr(mystring, 0,1) = '_' then 0 else 1 end
       , mystring

(not tested, but you get the idea)

i.e. you first order on whether or not the string starts with the character '_' or not, and then on the string itself.

As others have pointed out, your example renders this unecessary, but this is how you would do it in general. For instance, you could order all 5-character words first in a similar way:

order by 
       case when length(mystring) = 5 then 0 else 1 end
       , mystring

Upvotes: 1

Related Questions