how to replace multiple characters from a column of table in mysql

Question

i have a table containing more then 90,000 records where one field is phone_no.

i would like to replace the following special characters from phone_no column.

"(",")","/"," ","-","+"

the following query update only 1 character at a time.

//SQL Query i have used to update 
UPDATE notary_info SET mobile_phone = REPLACE(mobile_phone, '/', '')

is it possible to replace all above mentioned special characters in one mysql query?

Answer 1

Try nested REPLACE() function like:

UPDATE notary_info SET mobile_phone = 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(mobile_phone, '/', ''),'(',''),')',''),' ',''),'+',''),'-','');