2

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

Examples: 

"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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Abhijith
  • 69
  • 1
  • 10
  • 1
    If you just want the numbers this link has a useful function using Pattern Matching. https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string – bushell Jul 27 '17 at 15:40
  • 2
    Ouch....the accepted answer at that link is using loops. This can be done set based. I will post an answer shortly. – Sean Lange Jul 27 '17 at 15:46
  • 1
    Looks like scsimon beat me to it. I was going to do something similar. – Sean Lange Jul 27 '17 at 15:59
  • 1
    @SeanLange that'll probably be the last time i beat you--you're wicked fast most of the time – S3S Jul 27 '17 at 16:00
  • 1
    What if there are two phone numbers? How many different international formats will you potentially need to support? This can dictate what kind of parsing you need to do and how to present numbers that don't have the exact number of digits you might expect. Parsing blobs of text for things you *think* might be phone numbers can get quite hairy. – Aaron Bertrand Jul 27 '17 at 16:11

2 Answers2

2

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
values
('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?')

select
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

S3S
  • 24,809
  • 5
  • 26
  • 45
  • This breaks if the phone number happens to be separated with spaces. – bushell Jul 27 '17 at 15:56
  • no worries @Abhijith, but a word of advice... i'd try an normalize your data and keep this input field as strictly numeric to prevent this. – S3S Jul 27 '17 at 16:10
1

Try this way also using Patindex,Reverse ,Replace Functions

declare @Datatable table (c varchar(256))
insert into @Datatable
values
('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?')



 SELECT c AS VColumn,
REPLACE(REPLACE(REPLACE(REVERSE(SUBSTRING((c2),PATINDEX('%[0-9]%',(c2)),Len((c2)))),')',''),'-',''),' ','') AS ExtractedNUmber from
(
SELECT *,REVERSE(SUBSTRING(c,PATINDEX('%[0-9]%',c),LEN(c) )) AS C2 from @Datatable

)dt

Result

VColumn                                     ExtractedNUmber
--------------------------------------------------------------
This is a test 111-222-3344                 1112223344
Some Sample Text (111)-222-3344             1112223344
Hello there 111222 3344 / How are you?      1112223344
Hello there 111 222 3344 ? How are you?     1112223344
Hello there 111 222 3344. How are you?      1112223344