CODEWITHSUNDEEP
sql
Sam P
Sam P

Reputation: 113

SQL Phone Number Conversion

I have phone numbers in a SQL database stored in (xxx) xxx-xxxx format. is there any way to cast these as xxxxxxxxxx format instead?

Upvotes: 0

Views: 142

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

Assuming you are using Oracle or MySQL 8+, you could use REGEXP_REPLACE:

SELECT
    REGEXP_REPLACE('(914) 591-8563', '\((\d{3})\)\s*(\d{3})-(\d{4})', '\1\2\3')
FROM dual;

9145918563

This solution works via regex by capturing the 10 digits, and then generating a replacement of only digits. Explore the demo below using either Oracle or MySQL 8+:

Demo

Upvotes: 0

Zeki Gumus
Zeki Gumus

Reputation: 1484

Simply you can use REPLACE() function :

SELECT REPLACE(REPLACE(REPLACE(REPLACE('(XXX) XXX-XXXX','(',''),')',''),'-',''),' ','')

Upvotes: 1

Related Questions