Abhijith Abhijith - 2 years ago 96
SQL Question

How to extract phone number from varchar column in Sql server?

I have a varchar column in my table which can contain phone numbers in different formats along with some text in there .


"This is a test 111-222-3344"

"Some Sample Text (111)-222-3344"

"Hello there 1112223344 . How are you?"

How do I extract the phone numbers from this? I've looked up other solutions (Another Post), but they don't fit my requirements.

Thank you

Answer Source

Well, since they are in different formats, I'd extract them in the same format.

--Handles parentheses, commas, spaces, hyphens..
declare @table table (c varchar(256))
insert into @table
('This is a test 111-222-3344'),
('Some Sample Text (111)-222-3344'),
('Hello there 111222 3344 / How are you?'),
('Hello there 111 222 3344 ? How are you?'),
('Hello there 111 222 3344. How are you?')

replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','')
from @table

Partial Credit

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download