Extract numbers only from varchar field

Transact-SQL
1

Hello,

I have a phone number field in my database which is varchar and so it can contain characters like "+", "(" etc.

I would like to extract the numbers and remove the other characters.

I have googled the topic and tried a combination of substring and patindex but I can't seem to work it out.

If I had "386(+224)74769111908" in my field, how I would I achieve "38622474769111908"?

Thanks in advance

2

Use REPLACE

Select replace(replace(replace('386(+224)74769111908', '+', ''), '(', ''), ')', '')

Continue adding additional replace functions for all other characters found.

1 Like
3

Thanks @jeffw8713, this has worked perfectly!

4

You can find "all" the rogue chargers used, iteratively, using a query like this:

SELECT	TOP 100 MyPhoneColumn
FROM	dbo.MyTable
WHERE	MyPhoneColumn LIKE '%[^-0-9()+]%'

Just keep adding characters to the [collection] until you no longer get any phone numbers listed.

Note that the "-" needs to be at the start, as I have it.

5

Hi

I know this topic was long long long time ago

I am practicing my SQL skills

I have given a different solution
Copy pasted from google

This will get only the numbers .... much simpler
than a whole bunch of replaces looking for each character

I think
:slight_smile:
:slight_smile:

SQL mine 
DECLARE @var VARCHAR(100) = '386(+224)74769111908' 

    WHILE Patindex('%[^0-9]%', @var) <> 0 
      SET @var = Stuff(@var, Patindex('%[^0-9]%', @var), 1, '') 

SELECT @var
Result

image

1 Like