SQLTeam.com | Weblogs | Forums

Extract numbers only from varchar field


#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.


#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.