SQL Phone Number Conversion

Question

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?

Answered here: https://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters — tecshack, Jan 04 '19 at 13:50
How did this get 2 upvotes? "Remove non-numeric characters SQL", 'format phone numbers SQL', any of those Google searches show anything? — dfundako, Jan 04 '19 at 13:53

score 1 · Answer 1 · answered Jan 04 '19 at 13:49

1

Simply you can use REPLACE() function :

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

answered Jan 04 '19 at 13:49

Zeki Gumus

1,484
7
14

1

This could be shorter by using MySQL's REGEXP REPLACE https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql, but it's only available for MySQL 8.0 – Alexander van Oostenrijk Jan 04 '19 at 13:56

score 0 · Answer 2 · answered Jan 04 '19 at 13:58

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+:

SQL Phone Number Conversion

2 Answers2

Demo