strip non-numeric characters from a phone

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

strip non-numeric characters from a phone

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-20 : 14:14:41

phone numbers... how can you strip out any non-numeric values from a phone number?

for example, lets say a client provides data where the phones are like:

"(602) 555-1234"

and i want to just drop this to "6025551234"

ususally i would do something like:


replace(replace(replace(replace(phone,'(',''),')',''),' ',''),'-','')

but then sometimes, i'll get a file where it's like:

"(602) 555-1234"
"602-555-1234"
"602 555-1234"
"John"

so in this case, basically i wanna strip out anything that's not numeric, and then i can null anything where they len(phone)<10

any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-20 : 14:42:16

create table #p (phone varchar(20))
insert #p
select '(602) 555-1234' union all
select '602-555-1234' union all
select '602 555-1234' union all
select 'John'

;with n(n) as (select 1 union all select n+1 from n where n<50)
select phone, (select SUBSTRING(phone,n,1)
from n cross join #p
where SUBSTRING(phone,n,1) like '[0-9]' and phone=p2.phone
order by phone, n
for XML path('') )
from #p p2

drop table #p

Replace the temp table references with your own table name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources