trondina
asked on
SQL Server 2005 Format Phone Numbers
I have a multi table database that has a bunch of phone number fields in the various tables. I need a stored procedure that i can execute on the DB to scan all tables for the phone number fields and then format the phone numbers to one of two formats depending on the presence of an extension.
The format should be either:
(###) ###-####
or
(###) ###-#### ext. ##### the extension can be 1-5 digits
Please not teh singe space between the ) and first digit in the prefix as well as 2 spaces on each side of extension AND the period after "ext"
Existing phone numbers in these fields are all North American but may be dirty or incorrect for example:
(000) 768-2345 or (002) 234-4560
impossible or bad phone number. In these cases keep leading zero's..we will make user correct at the presentation level.
I can supply table names and phone number fields if you would like.
8 tables and about 15 or 16 phone number fields total.
Thank you in advance
The format should be either:
(###) ###-####
or
(###) ###-#### ext. ##### the extension can be 1-5 digits
Please not teh singe space between the ) and first digit in the prefix as well as 2 spaces on each side of extension AND the period after "ext"
Existing phone numbers in these fields are all North American but may be dirty or incorrect for example:
(000) 768-2345 or (002) 234-4560
impossible or bad phone number. In these cases keep leading zero's..we will make user correct at the presentation level.
I can supply table names and phone number fields if you would like.
8 tables and about 15 or 16 phone number fields total.
Thank you in advance
No doubt as soon as you convert the phone numbers to this new format, you will need to have them in the original format. Why not always store them in the original, unformatted manner and allow the display (front end) to format how the user desires?
So ... you really want a function to strip the non-numeric characters, then force the result into the (###)###-#### format ... and if there are more than 10 digits append "ext. " and the remaining digits.
Right?
Right?
I only say this because we see here on EE all time people trying to remove formatting from phone numbers.
http://www.projectdmx.com/tsql/strcleanup.aspx will take care of stripping the other characters ...
Here's a function ... and some test data ...
Here's a function ... and some test data ...
Create Function dbo.FormatPhoneNumber (@str as nvarchar(25)) returns nvarchar(25)
AS
Begin
WHILE PATINDEX( '%[^0-9]%', @str ) > 0
SET @str = REPLACE( @str, SUBSTRING( @str,
PATINDEX( '%[^0-9]%', @str ), 1 ), '' )
Declare @Formatted nvarchar(25)
set @Formatted = case
When len(@str) <7 then @str
When len(@str) =7 then substring(@str,1,3) + '-' + substring(@str,4,4)
When len(@str) between 8 and 10 then '(' + substring(@str,1,3) + ')' + substring(@str,4,3) + '-' + substring(@str,6,len(@str)-6)
else '(' + substring(@str,1,3) + ')' + substring(@str,4,3) + '-' + substring(@str,6,4) + ' ext. ' + substring(@str,10,len(@str)-10)
End
return @Formatted
End
go
Select [dbo].[FORMATPHONENUMBER] ('1234567890123')
Select [dbo].[FORMATPHONENUMBER] ('(123) 4567-8901x23')
Select [dbo].[FORMATPHONENUMBER] ('1234567')
Brandon's idea about only formatting the phone # for display is good. I would consider calling the function in the SELECT statements.
ASKER
Brandon:
The problem is that these phone numbers were brought in during a database conversion project. The data is dirty and there is no consistent format among them. Hence we are now defining this new format as the original and standard format that all phone numbers will be written as to this new DB.
The problem is that these phone numbers were brought in during a database conversion project. The data is dirty and there is no consistent format among them. Hence we are now defining this new format as the original and standard format that all phone numbers will be written as to this new DB.
ASKER
Daniel:
You are exactly correct in what we are basically attempting to accomplish. I just need to make sure the spaces appear correctly as well as the period after the "ext".
I will test the script you provided and get back to you soon.
You are exactly correct in what we are basically attempting to accomplish. I just need to make sure the spaces appear correctly as well as the period after the "ext".
I will test the script you provided and get back to you soon.
ASKER
Actually Daniel because I am not a DB expert, I need a complete script that i can just run against the DB that will do the complete job. Can you assemble me one and provide the instructions to run in SQL Server MgMt Studio 2005? I will increase the points to 1250.
Thanks in advance
Thanks in advance
Bug you have no way to enforce it.
You can use the below function to strip non-numeric values, then apply formatting.
this select shows how it will do the formatting:
;with PhoneNumbers as
(select PhoneNumber, dbo.fn_StripNonNumeric (phonenumber) strippedPH
from (select '1234567890' as phonenumber union select '412-555-1212' union select '412/555/9898' union select '41255512125547')a)
select '('+left(strippedPH,3)+') ' + substring (strippedPH, 4,3) + '-' + substring(strippedPH, 7,4)
+ case when len(strippedPH)>10 then ' ext. ' + right(strippedPH, len(strippedPH)-10) else '' end
from phonenumbers p
This will do the update:
;with PhoneNumbers as
(select PhoneNumber, dbo.fn_StripNonNumeric (phonenumber) strippedPH
from YourTable)
update y
set phonenumber = '('+left(strippedPH,3)+') ' + substring (strippedPH, 4,3) + '-' + substring(strippedPH, 7,4)
+ case when len(strippedPH)>10 then ' ext. ' + right(strippedPH, len(strippedPH)-10) else '' end
from yourtable y
join phonenumbers p
on y.phonenumber = p.phonenumber
You can use the below function to strip non-numeric values, then apply formatting.
this select shows how it will do the formatting:
;with PhoneNumbers as
(select PhoneNumber, dbo.fn_StripNonNumeric (phonenumber) strippedPH
from (select '1234567890' as phonenumber union select '412-555-1212' union select '412/555/9898' union select '41255512125547')a)
select '('+left(strippedPH,3)+') ' + substring (strippedPH, 4,3) + '-' + substring(strippedPH, 7,4)
+ case when len(strippedPH)>10 then ' ext. ' + right(strippedPH, len(strippedPH)-10) else '' end
from phonenumbers p
This will do the update:
;with PhoneNumbers as
(select PhoneNumber, dbo.fn_StripNonNumeric (phonenumber) strippedPH
from YourTable)
update y
set phonenumber = '('+left(strippedPH,3)+') ' + substring (strippedPH, 4,3) + '-' + substring(strippedPH, 7,4)
+ case when len(strippedPH)>10 then ' ext. ' + right(strippedPH, len(strippedPH)-10) else '' end
from yourtable y
join phonenumbers p
on y.phonenumber = p.phonenumber
if object_id('dbo.fn_StripNonNumeric') is not null
drop function dbo.fn_StripNonNumeric
go
create function dbo.fn_StripNonNumeric (@Input nvarchar(max))
returns nvarchar(max)
as
begin
while patindex('%[^0-9]%',@input)>0
set @input = substring(@input,1,patindex('%[^0-9]%',@input)-1) + substring(@input,patindex('%[^0-9]%',@input)+1, (datalength(@input)/2))
return @input
end
go
select dbo.fn_StripNonNumeric ('1234xasdf66')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BRANDON:
Thats great so far but now how do i go and run it against all the tables in the database?
Thats great so far but now how do i go and run it against all the tables in the database?
trondina, to write a script that would do it for all appropriate fields in all tables, we need to know how to identify appropriate fields. For example ... fields with 'Phone' somewhere in their names? (but that won't get FaxNum if you have that as a field ....) All varchar fields of length 17?
If you give us good criteria like that either of us can put together a script to apply the function to appropriate fields in all the tables.
If you give us good criteria like that either of us can put together a script to apply the function to appropriate fields in all the tables.
exactly how DanielWilson did in http:#22672422
But you would have to do it for each field that contains a phone number in each table as he pointed out in http:#22680870
But you would have to do it for each field that contains a phone number in each table as he pointed out in http:#22680870
ASKER
DANIEL:
here are the table and field d names of the phone numbers:
Database Name = Properties
TABLENAME.FIELDNAME
AlternateAddress.AltPhone
Association.AssocPrimaryPh one
Association.AssocSecondary Phone
Association.AssocFax
EmergencyContact.Emergency PrimaryPho ne
EmergencyContact.Emergency SecondaryP hone
ManagementCompany.MgmtPrim aryPhone
ManagementCompany.MgmtSeco ndaryPhone
ManagementCompany.MgmtFax
Owner.Owner1PrimaryPhone
Owner.Owner1SecondaryPhone
Owner.Owner2PrimaryPhone
Owner.Owner2SecondaryPhone
Renter.RenterPhone
Resource.ResourcePhone
Resource.ResourceFax
Resource.ContactPhone
all fields are varchar(30) and allow NULLS
any other information you need?
here are the table and field d names of the phone numbers:
Database Name = Properties
TABLENAME.FIELDNAME
AlternateAddress.AltPhone
Association.AssocPrimaryPh
Association.AssocSecondary
Association.AssocFax
EmergencyContact.Emergency
EmergencyContact.Emergency
ManagementCompany.MgmtPrim
ManagementCompany.MgmtSeco
ManagementCompany.MgmtFax
Owner.Owner1PrimaryPhone
Owner.Owner1SecondaryPhone
Owner.Owner2PrimaryPhone
Owner.Owner2SecondaryPhone
Renter.RenterPhone
Resource.ResourcePhone
Resource.ResourceFax
Resource.ContactPhone
all fields are varchar(30) and allow NULLS
any other information you need?
Here is with my function:
update AlternateAddress
set AltPhone = dbo.fn_formatphonenumber(A ltPhone)
update Association
set AssocPrimaryPhone = dbo.fn_formatphonenumber(A ssocPrimar yPhone)
update Association
set AssocSecondaryPhone = dbo.fn_formatphonenumber(A ssocSecond aryPhone)
update Association
set AssocFax = dbo.fn_formatphonenumber(A ssocFax)
update EmergencyContact
set EmergencyPrimaryPhone = dbo.fn_formatphonenumber(E mergencyPr imaryPhone )
update EmergencyContact
set EmergencySecondaryPhone = dbo.fn_formatphonenumber(E mergencySe condaryPho ne)
update ManagementCompany
set MgmtPrimaryPhone = dbo.fn_formatphonenumber(M gmtPrimary Phone)
update ManagementCompany
set MgmtSecondaryPhone = dbo.fn_formatphonenumber(M gmtSeconda ryPhone)
update ManagementCompany
set MgmtFax = dbo.fn_formatphonenumber(M gmtFax)
update Owner
set Owner1PrimaryPhone = dbo.fn_formatphonenumber(O wner1Prima ryPhone)
update Owner
set Owner1SecondaryPhone = dbo.fn_formatphonenumber(O wner1Secon daryPhone)
update Owner
set Owner2PrimaryPhone = dbo.fn_formatphonenumber(O wner2Prima ryPhone)
update Owner
set Owner2SecondaryPhone = dbo.fn_formatphonenumber(O wner2Secon daryPhone)
update Renter
set RenterPhone = dbo.fn_formatphonenumber(R enterPhone )
update Resource
set ResourcePhone = dbo.fn_formatphonenumber(R esourcePho ne)
update Resource
set ResourceFax = dbo.fn_formatphonenumber(R esourceFax )
update Resource
set ContactPhone = dbo.fn_formatphonenumber(C ontactPhon e)
With DWs:
update AlternateAddress
set AltPhone = dbo.formatphonenumber(AltP hone)
update Association
set AssocPrimaryPhone = dbo.formatphonenumber(Asso cPrimaryPh one)
update Association
set AssocSecondaryPhone = dbo.formatphonenumber(Asso cSecondary Phone)
update Association
set AssocFax = dbo.formatphonenumber(Asso cFax)
update EmergencyContact
set EmergencyPrimaryPhone = dbo.formatphonenumber(Emer gencyPrima ryPhone)
update EmergencyContact
set EmergencySecondaryPhone = dbo.formatphonenumber(Emer gencySecon daryPhone)
update ManagementCompany
set MgmtPrimaryPhone = dbo.formatphonenumber(Mgmt PrimaryPho ne)
update ManagementCompany
set MgmtSecondaryPhone = dbo.formatphonenumber(Mgmt SecondaryP hone)
update ManagementCompany
set MgmtFax = dbo.formatphonenumber(Mgmt Fax)
update Owner
set Owner1PrimaryPhone = dbo.formatphonenumber(Owne r1PrimaryP hone)
update Owner
set Owner1SecondaryPhone = dbo.formatphonenumber(Owne r1Secondar yPhone)
update Owner
set Owner2PrimaryPhone = dbo.formatphonenumber(Owne r2PrimaryP hone)
update Owner
set Owner2SecondaryPhone = dbo.formatphonenumber(Owne r2Secondar yPhone)
update Renter
set RenterPhone = dbo.formatphonenumber(Rent erPhone)
update Resource
set ResourcePhone = dbo.formatphonenumber(Reso urcePhone)
update Resource
set ResourceFax = dbo.formatphonenumber(Reso urceFax)
update Resource
set ContactPhone = dbo.formatphonenumber(Cont actPhone)
update AlternateAddress
set AltPhone = dbo.fn_formatphonenumber(A
update Association
set AssocPrimaryPhone = dbo.fn_formatphonenumber(A
update Association
set AssocSecondaryPhone = dbo.fn_formatphonenumber(A
update Association
set AssocFax = dbo.fn_formatphonenumber(A
update EmergencyContact
set EmergencyPrimaryPhone = dbo.fn_formatphonenumber(E
update EmergencyContact
set EmergencySecondaryPhone = dbo.fn_formatphonenumber(E
update ManagementCompany
set MgmtPrimaryPhone = dbo.fn_formatphonenumber(M
update ManagementCompany
set MgmtSecondaryPhone = dbo.fn_formatphonenumber(M
update ManagementCompany
set MgmtFax = dbo.fn_formatphonenumber(M
update Owner
set Owner1PrimaryPhone = dbo.fn_formatphonenumber(O
update Owner
set Owner1SecondaryPhone = dbo.fn_formatphonenumber(O
update Owner
set Owner2PrimaryPhone = dbo.fn_formatphonenumber(O
update Owner
set Owner2SecondaryPhone = dbo.fn_formatphonenumber(O
update Renter
set RenterPhone = dbo.fn_formatphonenumber(R
update Resource
set ResourcePhone = dbo.fn_formatphonenumber(R
update Resource
set ResourceFax = dbo.fn_formatphonenumber(R
update Resource
set ContactPhone = dbo.fn_formatphonenumber(C
With DWs:
update AlternateAddress
set AltPhone = dbo.formatphonenumber(AltP
update Association
set AssocPrimaryPhone = dbo.formatphonenumber(Asso
update Association
set AssocSecondaryPhone = dbo.formatphonenumber(Asso
update Association
set AssocFax = dbo.formatphonenumber(Asso
update EmergencyContact
set EmergencyPrimaryPhone = dbo.formatphonenumber(Emer
update EmergencyContact
set EmergencySecondaryPhone = dbo.formatphonenumber(Emer
update ManagementCompany
set MgmtPrimaryPhone = dbo.formatphonenumber(Mgmt
update ManagementCompany
set MgmtSecondaryPhone = dbo.formatphonenumber(Mgmt
update ManagementCompany
set MgmtFax = dbo.formatphonenumber(Mgmt
update Owner
set Owner1PrimaryPhone = dbo.formatphonenumber(Owne
update Owner
set Owner1SecondaryPhone = dbo.formatphonenumber(Owne
update Owner
set Owner2PrimaryPhone = dbo.formatphonenumber(Owne
update Owner
set Owner2SecondaryPhone = dbo.formatphonenumber(Owne
update Renter
set RenterPhone = dbo.formatphonenumber(Rent
update Resource
set ResourcePhone = dbo.formatphonenumber(Reso
update Resource
set ResourceFax = dbo.formatphonenumber(Reso
update Resource
set ContactPhone = dbo.formatphonenumber(Cont
Actually.... that was a text editor macro... i didn't realize there were multiple columns in the same tables:
here is an updated:
here is an updated:
update AlternateAddress
set AltPhone = dbo.formatphonenumber(AltPhone)
update Association
set AssocPrimaryPhone = dbo.formatphonenumber(AssocPrimaryPhone)
,AssocSecondaryPhone = dbo.formatphonenumber(AssocSecondaryPhone)
,AssocFax = dbo.formatphonenumber(AssocFax)
update EmergencyContact
set EmergencyPrimaryPhone = dbo.formatphonenumber(EmergencyPrimaryPhone)
,EmergencySecondaryPhone = dbo.formatphonenumber(EmergencySecondaryPhone)
update ManagementCompany
set MgmtPrimaryPhone = dbo.formatphonenumber(MgmtPrimaryPhone)
,MgmtSecondaryPhone = dbo.formatphonenumber(MgmtSecondaryPhone)
,MgmtFax = dbo.formatphonenumber(MgmtFax)
update Owner
set Owner1PrimaryPhone = dbo.formatphonenumber(Owner1PrimaryPhone)
,Owner1SecondaryPhone = dbo.formatphonenumber(Owner1SecondaryPhone)
,Owner2PrimaryPhone = dbo.formatphonenumber(Owner2PrimaryPhone)
,Owner2SecondaryPhone = dbo.formatphonenumber(Owner2SecondaryPhone)
update Renter
set RenterPhone = dbo.formatphonenumber(RenterPhone)
update Resource
set ResourcePhone = dbo.formatphonenumber(ResourcePhone)
,ResourceFax = dbo.formatphonenumber(ResourceFax)
,ContactPhone = dbo.formatphonenumber(ContactPhone)
ASKER
Thanks guys. I dont see anyway to increase my question point value so all i can do is split 500 between you.
I appreciate the help.
I appreciate the help.
500 points is the maximum question value.