Wednesday, January 16, 2008

Database Script for US Phone No. Validation

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

No comments:

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape