Database Script for Validating Previously stored US Phone Numbers.
Example: You were not storing phone no. in format 1234567890 and to convert it to 123-456-7890 format following script would be useful.
Database Script for Formatting USA Phone Number
Note: Red Mark are the considerations which you need to taken care of for individual table.
DECLARE c1 CURSOR READ_ONLY
FOR
select [Phone-Number],[Last Name],[First Name] from Temp_Boniva_Speakers
declare @PhoneNum varchar(50)
declare @NewPhoneNum varchar(50)
declare @FirstDash varchar(1)
declare @SecondDash varchar(1)
declare @LeftNum varchar(3)
declare @MiddleNum varchar(3)
declare @LastNum varchar(4)
--Extra Fields for checking uniqueness of record
declare @FirstName varchar(50)
declare @LastName varchar(50)
declare @Email varchar(200)
open c1
FETCH NEXT FROM c1
Into @PhoneNum, @LastName, @FirstName
WHILE @@FETCH_STATUS = 0
BEGIN
set @PhoneNum = ltrim(@PhoneNum)
set @PhoneNum = rtrim(@PhoneNum)
--PRINT @PhoneNum
/********** Logic for conversion **************/
set @FirstDash = SUBSTRING(@PhoneNum,4,1)
set @SecondDash = SUBSTRING(@PhoneNum,8,1)
--When Proper Dash are their (Eg: 123-456-7890)
if @FirstDash = '-' and @SecondDash = '-'
begin
set @NewPhoneNum = @PhoneNum --Don't Change
end
--When Space is their instead of dash (Eg: 123 456 7890)
else if ascii(@FirstDash) = 32 and ascii(@SecondDash) = 32
begin
set @NewPhoneNum = REPLACE(@PhoneNum,@FirstDash,'-')
end
--When Given format is their (Eg: (123) 456-7890)
else if SUBSTRING(@PhoneNum,1,1) = '(' and SUBSTRING(@PhoneNum,4,1) = ')' and ascii(SUBSTRING(@PhoneNum,5,1)) = 32
begin
set @LeftNum = SUBSTRING(@PhoneNum,2,3)
set @MiddleNum = SUBSTRING (@PhoneNum,6,3)
set @LastNum = SUBSTRING (@PhoneNum,10,4)
set @NewPhoneNum = @LeftNum + '-' + @MiddleNum + '-' + @LastNum
end
--When No Dash is their (Eg: 1234567890)
else if IsNumeric(@PhoneNum) = 1
begin
set @LeftNum = SUBSTRING(@PhoneNum,1,3)
set @MiddleNum = SUBSTRING (@PhoneNum,4,3)
set @LastNum = SUBSTRING (@PhoneNum,7,4)
set @NewPhoneNum = @LeftNum + '-' + @MiddleNum + '-' + @LastNum
end
else --For Unknown Format Don't Loose the data
begin
set @NewPhoneNum = @PhoneNum
end
--Update
update Temp_Boniva_Speakers
set [Phone-Number]=@NewPhoneNum
where
--Uniqueness Checking
[Last Name] = @LastName and
[First Name] = @FirstName
--select @NewPhoneNum as NewPhone, @PhoneNum as OldPhone, @LastName, @FirstName
FETCH NEXT FROM c1
Into @PhoneNum, @LastName, @FirstName
END
CLOSE c1
DEALLOCATE c1
