How to ignore special characters when using ORDER BY in a MySQL query

I have the following MySQL query that provides data in a Python webpage. I have a list of song titles on the webpage, and I want it to be in alphabetical order, ignoring punctuation and spaces. My MySQL database is encoded in UTF-8 encoding, and some punctuation characters that need to be ignored are special characters such as curly apostrophes, etc.

SELECT * FROM Tracks\
JOIN Artists USING (ArtistID)\
JOIN Albums USING (AlbumID)\
JOIN Songs USING (SongID)\
ORDER BY UPPER(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(\
REPLACE(SoName, ' ', ''), /* space */\
                        ',', ''), /* comma */\
                        '.', ''), /* period */\
                        ':', ''), /* colon */\
                        ';', ''), /* semicolon */\
                        '!', ''), /* exclamation point */\
                        '?', ''), /* question mark */\
                   '\u201c', ''), /* left curly double quote */\
                   '\u201d', ''), /* right curly double quote */\
                   '\u2019', ''), /* right curly single quote (apostrophe) */\
                   '\u2013', ''), /* n-dash */\
                   '\u2014', ''), /* m-dash */\
                   '\u2026', '') /* ellipsis */), (SongID), UPPER(AlTitle)

REPLACE works fine in my query for non-specific characters like space, comma, period, etc., but seems to miss special characters.

I assume that the characters should be written in a different format. I tried the following without success: REPLACE(SoName, '\u2026', '') REPLACE(SoName, u'\2026', '') REPLACE(SoName, 0xE280A6, '')...

+5
2

MySQL escape- . 7 : , .

, ( - CHAR()).

+2

Python, MySQL:

from __future__ import unicode_literals

!

0

All Articles