extract phone number from string format either (xxx)xxxxxxx or (xxx)xxx-xxxx

Question

I have phone number field in either of these two formats (xxx)xxxxxxx or (xxx)xxx-xxxx

I want to extract and format the number as xxxxxxxxxx, number only. Is there a simple way in sql select statement?

Possible duplicate of [Query to get only numbers from a string](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) — Bob Kaufman, Jul 07 '17 at 20:40

score 0 · Answer 1 · answered Jul 07 '17 at 20:40

It is always difficult to answer coding questions without an example of the code with which you are working. That being said, something like this might be what you are look for:

SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'(##########') AS [Formatted Phone]
FROM tbl_sample

score 0 · Accepted Answer · answered Jul 07 '17 at 20:46

0

SELECT 
column_sample,
REPLACE(REPLACE(REPLACE(phone , '(', '') , ')', '') , '-', '') as phone
FROM tbl_sample;

answered Jul 07 '17 at 20:46

Kemal Güler

608
1
6
21

Thank you, However, there is still space in the place of '(' and ')'. Is there a way to remove them? – Jackie G Ma Jul 07 '17 at 20:54
Can you please give an example? @JackieGMa – Kemal Güler Jul 07 '17 at 20:57
copied from results 206 7891381, should be 2067891381, original format (206)789-1381 – Jackie G Ma Jul 07 '17 at 20:58
There should be no space. But you can add one more replace which replaces space. REPLACE(REPLACE(REPLACE(REPLACE(phone , '(', '') , ')', '') , '-', ''), ' ', '') as phone – Kemal Güler Jul 07 '17 at 21:07

extract phone number from string format either (xxx)xxxxxxx or (xxx)xxx-xxxx

2 Answers2