MySQL sorting with symbols as first letter

Question

I have a MySQL table with these rows:

¡Hola!
¿Qué tal?
Fine
Hello
Adiós
How are you?

How can I sort them to obtain this result:

Adiós
Fine
Hello
¡Hola!
How are you?
¿Qué tal?

I want MySQL to ignore non alphanumeric characters.
It's possible to do this fast without making a new column with a simplified value?

What sort order do you want after removing symbols? English? Spanish? Another? — gbn, Aug 27 '12 at 11:55

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

It's a notorious pain in the neck to do this in MySQL, unfortunately.

Try this:

ORDER BY replace(replace(phrase,'¿',''),'¡','')

to remove the Spanish-language prefix punctuation from your phrases as they are sorted. One wishes MySQL had a REGEXP_REPLACE function.

As for the performance, you should be fine with ORDER BY functions unless you have zillions of phrases. Dozens, no problem.

edited May 23 '17 at 12:04

Community

answered Aug 27 '12 at 12:18

O. Jones

1

I have around 30.000 phrases (movie titles) and my server must handle peaks of 100+ requests per second. I believe that the best approach is to make a new column with the symbols stripped. – Peter Aug 27 '12 at 14:42
I agree, if you're working at that scale. But you might want to look into full-text indexing. – O. Jones Aug 27 '12 at 20:18

1 Answers1