mySQL sorts underscore last.

Question

Hi, I have a column in my table that stores a string (it is a text column):

varchar(16) latin1_swedish_ci

The problem is, when I say "ORDER BY name ASC" it returns words starting with an underscore at the end. This is an example it returned:

-a
-mmddd2
-z
-z3
aaa
b
c
t
_a
___-

I bet I can use php to sort, but is there an easy way to make mySQL put the underscores after the dashes? I am doing this so it will match the output of the javascript sort function.

I'm trying to get:

-a
-mmddd2
-z
-z3
_a
___-
aaa
b
c
t

Answer 1

Collations define the sort order, if none of the available collations use the sort order you need you can define your own, as explained here.

Answer 2

I'm not saying this is an elegant solution, but you could try doing something like this:

select name
from customers
order by replace(name, '_', '-+') asc;

Just replace the + with whatever will put the prefixed underscore values after the dash.