Complex mysql ORDER BY

Question

Executing the following query:

SELECT title FROM table ORDER BY title

gives me:

"Hello"
"Zebra"
Apple
Beta
Cactus

How should I ORDER the first alphabetical character to get:

Apple
Beta
Cactus
"Hello"
"Zebra"

?

+5

David542 Jul 31 '12 at 10:16

3 answers

...ORDER BY REPLACE(title, '"', '');

+3

davek Jul 31 '12 at 10:18

SELECT title FROM table ORDER BY TRIM(LEADING '"' FROM title)

+3

Shakti singh Jul 31 '12 at 10:19

Omesh · Accepted Answer · 2012-07-31T10:17:55+0000

You will need to remove the quotes before sorting the data. You can easily remove them using a function TRIMin MySQL like:

SELECT title 
FROM table 
ORDER BY TRIM(BOTH '"' FROM title);