CODEWITHSUNDEEP
mysql
Mukta Chourishi
Mukta Chourishi

Reputation: 64

How to replace all special characters and spaces in a table column?

I have a table named "contacts" with below fields

id | phone_mobile

1  | 9988011223
2  | 00-12-123456
3  | 91-8876980987
4  | (91) 88990099777

I want a select query which will return below output

id | phone_mobile

1  | 9988011223
2  | 0012123456
3  | 918876980987
4  | 9188990099777

Upvotes: 0

Views: 1460

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

For a known set of characters you can use replace() function chaining something as

mysql> select replace(replace(replace('00-12-123456','-',''),'(',''),')','');
+----------------------------------------------------------------+
| replace(replace(replace('00-12-123456','-',''),'(',''),')','') |
+----------------------------------------------------------------+
| 0012123456                                                     |
+----------------------------------------------------------------+

So in your case it could be

select 
id,
replace(replace(replace(replace(phone_mobile,'-',''),'(',''),')',''),' ','') as phone_mobile
from table_name

If there is a long list of characters to be replaced then its better to use the application level to do the job since the chaining of replace becomes really messy.

Upvotes: 3

Related Questions