CODEWITHSUNDEEP
sqlsql-server
Abhijith
Abhijith

Reputation: 69

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.

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

Upvotes: 2

Views: 6204

Answers (3)

Artem Vorobev
Artem Vorobev

Reputation: 11

You can solve your problem using two functions: translate and replace:

create table #mob (
comment varchar(100)
    )

insert into #mob (comment)
values
 ('This is a test 111-222-3344')
,('Some Sample Text (111)-222-3344')
,('Hello there 1112223344')

select
comment
,replace(translate(replace(comment, replace(translate(comment, '0123456789-()', '#############'), '#', ''), ''), '()-', '###'), '#', '') [number]
from mob

Result:

comment                           number
--------------------------------------------
This is a test 111-222-3344       1112223344
Some Sample Text (111)-222-3344   1112223344
Hello there 1112223344            1112223344

Upvotes: 1

user7715598
user7715598

Reputation:

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

Upvotes: 1

S3S
S3S

Reputation: 25142

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

Upvotes: 2

Related Questions