Does anyone have a code sample where I can to remove all special characters from data stored in MYSQL?
I need to remove the following special characters:
: ~!@#$%*()_+{}[];':"<>?
views:
37answers:
1
A:
Try this... It looks terrible, but it works
SELECT * FROM Film where REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Film.name,':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'_',''),'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?','') = ?
This one will also strip out space:
SELECT * FROM Film where REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Film.name,':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'_',''),'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','') = ?
cjavapro
2010-10-20 21:48:22
thanks , but is it cause many slow , in query ? because multi replace ....
meotimdihia
2010-10-20 23:12:51
That is because it has to do the replace for every single row. Is it possible to add a special column `name_search` for the one without special characters? You could keep it up to date every night like this `UPDATE Film SET name_search = REPLACE....(name, ....)` Then you could run the search on name_search and the replacing will already be done. It would also allow you to use the LIKE operator and make better uses of indexes which would make things faster
cjavapro
2010-10-21 12:42:12