Sqlite replace() for replacing multiple characters

Question

I have database table Contacts with columns Mobile, officePhone.

Here users will save data as follows.

mobile=+91 (937) 2582 6433
officephone= (843)-6486-6783

Now i need to retrieve phone numbers with out any special characters

Some how i found following query,

Select REPLACE(mobile,'+','') as mobile , REPLACE(officePhone,'+','') as officePhone from Contacts;

This will remove only "+" symbol but i want to remove all special symbols including space. Please help me in this regard.

Answer 1

It's not exactly pretty, but you could nest the replace() functions:

replace(replace(replace(replace(mobile,' ',''),'-',''),'(',''),')','')

Agreed, if possible you can format outside of sqlite, but that is not always wanted/possible. E.g. I had a case where the replaces were needed for the WHERE part.