Re: [Emerald] SQL QUERY: Extracting Spaces and/or '-' from Phone Numbers

Dan Tang ( (no email) )
Sun, 6 Feb 2000 01:05:22 +0800

Here it is.

declare @phonehome varchar(50)
declare @phonework varchar(50)
declare @phonefax varchar(50)
declare @temphome varchar(50)
declare @tempwork varchar(50)
declare @tempfax varchar(50)
declare @i integer

declare curPhone Cursor
for select phonehome, phonework,phonefax from masteraccounts
for update

open curPhone
fetch next from curphone into @phonehome,@phonework,@phonefax
while @@Fetch_Status=0
Begin
select @temphome=''
select @tempwork=''
select @tempfax=''
select @i=datalength(@phonehome)
while @i>0
begin
if Substring(@phonehome,@i,1)in ('1','2','3','4','5','6','7','8','9','0')
select @temphome=Substring(@phonehome,@i,1)+@temphome
select @i=@i-1
end

select @i=datalength(@phonefax)
while @i>0
begin
if Substring(@phonefax,@i,1)in ('1','2','3','4','5','6','7','8','9','0')
select @tempfax=Substring(@phonefax,@i,1)+@tempfax
select @i=@i-1
end

select @i=datalength(@phonework)
while @i>0
begin
if Substring(@phonework,@i,1)in ('1','2','3','4','5','6','7','8','9','0')
select @tempwork=Substring(@phonework,@i,1)+@tempwork
select @i=@i-1
end

update masteraccounts
set phonehome=@temphome,
phonework=@tempwork,
phonefax=@tempfax
where current of curphone

fetch next from curphone into @phonehome,@phonework,@phonefax
End
close curphone
deallocate curphone

-----Original Message-----
From: Rudy Komsic <rudyk@cyberglobe.net>
To: emerald@iea-software.com <emerald@iea-software.com>
Date: Saturday, February 05, 2000 7:21 PM
Subject: [Emerald] SQL QUERY: Extracting Spaces and/or '-' from Phone
Numbers

>Hi,
>
>I am not a SQL Guru but I would like to be able to remove all hyphens
and/or
>spaces from the phone numbers cause we would like to create some ANI
>Checking vs the Phone list to allow clients to change their passwords
and/or
>access their account info.
>
>So if someone's Home phone number is 123 345-5678, I would like to be able
>to rewrite that in a stored procedure which would run nightly to rewrite
all
>phone numbers in ANI compatible format "1233455678"
>
>Any suggestions on this one?
>
>
>
>
>For more information about this list (including removal) go to:
>http://www.iea-software.com/support/maillists/liststart

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart