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, ' ', ''), \
',', ''), \
'.', ''), \
':', ''), \
';', ''), \
'!', ''), \
'?', ''), \
'\u201c', ''), \
'\u201d', ''), \
'\u2019', ''), \
'\u2013', ''), \
'\u2014', ''), \
'\u2026', '') ), (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, '')...