MYSQL - Order column by ASCII Value

Question

I have a column with texts, sorted by ASCII it should be ordered as:

-  (hyphen)
0
1  (numbers)
2
A  (uppercase)
B
_  (underscore)
a
b  (lowercase)
c

However it is being ordered as:

-  (hyphen)
0
1  (numbers)
2
a
b  (lowercase)
c
A
B  (uppercase)
C
_  (underscore)

How can I do the sorting by ASCII value?

Answer 1

The sort order is controlled by the collation. You can use the BINARY collation to sort by raw bytes, which in the case of ASCII data will cause it to sort by ASCII value. See https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

SELECT ...
FROM mytable
ORDER BY BINARY mycolumn

This will be more flexible than using the ASCII() function because that function only returns the ASCII value of the first character. Using the BINARY collation allows sorting by the full string.

Answer 2

You could use ASCII:

SELECT *
FROM tab
ORDER BY ASCII(col_name) ASC