April 22, 2013 at 9:50 am
I need to check to see if string contains (,-, / or . and pull out only the numbers from the string....
212/2222255should be 212-222-2255
212.222.5555should be 212-2225-555
(212).222.5555 should be 212-222-5555
212-222-5555should be 212-222-5555
212-222-5555 EXT 511should be 212-222-5555 511
what would be the best way to code this?
April 22, 2013 at 10:15 am
There's no easy way to do this in sql.
However there are many.
For code consolidation use a inline function.
But you need to figure out what you want to do when there are not 10 numbers.
Pad the left with zeros' What?
First use multiple replace functions to get rid of all characters.
then concatenate a new string using Stuff or substring.
April 22, 2013 at 12:46 pm
you could do this;
create table #MyHead (PhoneNumber varchar(100))
insert into #MyHead VALUES ('212/2222255'),
('212.222.5555'),
('(212).222.5555'),
('212-222-5555'),
('212-222-5555 EXT 511')
;
WITH CTE AS (
select
CASE
WHEN charindex('/', PhoneNumber) > 0 then REPLACE(PhoneNumber,'/','')
WHEN charindex('.', PhoneNumber) > 0 then REPLACE(PhoneNumber,'.','')
WHEN charindex('(', PhoneNumber) > 0 then REPLACE(PhoneNumber,'(','')
WHEN charindex(')', PhoneNumber) > 0 then REPLACE(PhoneNumber,')','')
WHEN charindex('-', PhoneNumber) > 0 then REPLACE(PhoneNumber,'-','')
WHEN charindex('EXT', PhoneNumber) > 0 then REPLACE(PhoneNumber,'EXT',' ')
ELSE PhoneNumber END as PhoneNumber
from #MyHead), CTE2 AS (
select
CASE
WHEN charindex('/', PhoneNumber) > 0 then REPLACE(PhoneNumber,'/','')
WHEN charindex('.', PhoneNumber) > 0 then REPLACE(PhoneNumber,'.','')
WHEN charindex('(', PhoneNumber) > 0 then REPLACE(PhoneNumber,'(','')
WHEN charindex(')', PhoneNumber) > 0 then REPLACE(PhoneNumber,')','')
WHEN charindex('-', PhoneNumber) > 0 then REPLACE(PhoneNumber,'-','')
WHEN charindex('EXT', PhoneNumber) > 0 then REPLACE(PhoneNumber,'EXT','')
ELSE PhoneNumber END as PhoneNumber
from CTE), CTE3 AS (
select
CASE
WHEN charindex('/', PhoneNumber) > 0 then REPLACE(PhoneNumber,'/','')
WHEN charindex('.', PhoneNumber) > 0 then REPLACE(PhoneNumber,'.','')
WHEN charindex('(', PhoneNumber) > 0 then REPLACE(PhoneNumber,'(','')
WHEN charindex(')', PhoneNumber) > 0 then REPLACE(PhoneNumber,')','')
WHEN charindex('-', PhoneNumber) > 0 then REPLACE(PhoneNumber,'-','')
WHEN charindex('EXT', PhoneNumber) > 0 then REPLACE(PhoneNumber,'EXT','')
ELSE PhoneNumber END as PhoneNumber
from CTE2)
select STUFF(STUFF(PhoneNumber,4,0,'-'), 8,0, '-') from CTE3
drop table #MyHead
April 23, 2013 at 1:05 pm
If you want something big, slow, and ugly, there's this:
with t1 as (
select
ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace(replace(replace(replace(
[phonenumber]
,'-',''),'(',''),')',''),'*',''),'/',''),'\',''),'.',''),'+','')
,'@',''),'#',''),'$',''),'%',''),'^',''),'&',''),',',''),'~','')
,'`',''),'_',''),'=',''),'{',''),'}',''),'[',''),']',''),'|','')
,':',''),';',''),'<',''),'>',''),'?',''),'a',''),'b',''),'c','')
,'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k','')
,'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s','')
,'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),' ',''))) as phonenumber
from YOURTABLE
)
select case
when len(phonenumber) = 10 and phonenumber not like '1%' then stuff(stuff(phonenumber,4,0,'-'), 8,0, '-')
when len(phonenumber) > 10 and phonenumber not like '1%' then stuff(stuff(substring(phonenumber,1,10),4,0,'-'), 8,0, '-')
when len(phonenumber) > 10 and phonenumber like '1%' then stuff(stuff(substring(phonenumber,2,11),4,0,'-'), 8,0, '-')
else phonenumber
end
as [phone]
from t1
The cases are possibly incomplete, like for when someone enters too few digits, but this should cover most things.
April 23, 2013 at 1:07 pm
Thanks guys. It worked perfect.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply