?
Solved

SQL Server 2005 Format Phone Numbers

Posted on 2008-10-06
19
Medium Priority
?
1,685 Views
Last Modified: 2008-10-09
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
0
Comment
Question by:trondina
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 5
19 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22653351
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?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22653358
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22653360
I only say this because we see here on EE all time people trying to remove formatting from phone numbers.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22653590
http://www.projectdmx.com/tsql/strcleanup.aspx will take care of stripping the other characters ...

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')

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22653638
Brandon's idea about only formatting the phone # for display is good.  I would consider calling the function in the SELECT statements.
0
 

Author Comment

by:trondina
ID: 22672147
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.
0
 

Author Comment

by:trondina
ID: 22672176
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.
0
 

Author Comment

by:trondina
ID: 22672235
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22672269
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


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')

Open in new window

0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1000 total points
ID: 22672422
Using the function I provided:

Update MyTable Set MyPhoneNUmber = [dbo].[FORMATPHONENUMBER](MyPhoneNumber)

Open in new window

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1000 total points
ID: 22672907
I'd go with http:#22653590.  It's clean because it handles all the logic inside of one function whereas I split it into a function and an update:


Had I done a single function (which I should have) I would have done this:



if object_id('dbo.fn_FormatPhoneNumber') is not null
   drop function dbo.fn_FormatPhoneNumber 
go
create function dbo.fn_FormatPhoneNumber (@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))
set @input='('+left(@input,3)+') '  + substring (@input, 4,3) + '-' + substring(@input, 7,4)
+ case when len(@input)>10 then ' ext. ' + right(@input, len(@input)-10) else '' end
return @input
end
go
select dbo.fn_FormatPhoneNumber ('1234xasdf66123123123')

Open in new window

0
 

Author Comment

by:trondina
ID: 22680773
BRANDON:

Thats great so far but now how do i go and run it against all the tables in the database?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22680870
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22681106
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
0
 

Author Comment

by:trondina
ID: 22682098
DANIEL:

here are the table and field d names of the phone numbers:

Database Name = Properties

TABLENAME.FIELDNAME

AlternateAddress.AltPhone
Association.AssocPrimaryPhone
Association.AssocSecondaryPhone
Association.AssocFax
EmergencyContact.EmergencyPrimaryPhone
EmergencyContact.EmergencySecondaryPhone
ManagementCompany.MgmtPrimaryPhone
ManagementCompany.MgmtSecondaryPhone
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?

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682147
Here is with my function:

update AlternateAddress
set AltPhone = dbo.fn_formatphonenumber(AltPhone)

update Association
set AssocPrimaryPhone = dbo.fn_formatphonenumber(AssocPrimaryPhone)

update Association
set AssocSecondaryPhone = dbo.fn_formatphonenumber(AssocSecondaryPhone)

update Association
set AssocFax = dbo.fn_formatphonenumber(AssocFax)

update EmergencyContact
set EmergencyPrimaryPhone = dbo.fn_formatphonenumber(EmergencyPrimaryPhone)

update EmergencyContact
set EmergencySecondaryPhone = dbo.fn_formatphonenumber(EmergencySecondaryPhone)

update ManagementCompany
set MgmtPrimaryPhone = dbo.fn_formatphonenumber(MgmtPrimaryPhone)

update ManagementCompany
set MgmtSecondaryPhone = dbo.fn_formatphonenumber(MgmtSecondaryPhone)

update ManagementCompany
set MgmtFax = dbo.fn_formatphonenumber(MgmtFax)

update Owner
set Owner1PrimaryPhone = dbo.fn_formatphonenumber(Owner1PrimaryPhone)

update Owner
set Owner1SecondaryPhone = dbo.fn_formatphonenumber(Owner1SecondaryPhone)

update Owner
set Owner2PrimaryPhone = dbo.fn_formatphonenumber(Owner2PrimaryPhone)

update Owner
set Owner2SecondaryPhone = dbo.fn_formatphonenumber(Owner2SecondaryPhone)

update Renter
set RenterPhone = dbo.fn_formatphonenumber(RenterPhone)

update Resource
set ResourcePhone = dbo.fn_formatphonenumber(ResourcePhone)

update Resource
set ResourceFax = dbo.fn_formatphonenumber(ResourceFax)

update Resource
set ContactPhone = dbo.fn_formatphonenumber(ContactPhone)


With DWs:
update AlternateAddress
set AltPhone = dbo.formatphonenumber(AltPhone)

update Association
set AssocPrimaryPhone = dbo.formatphonenumber(AssocPrimaryPhone)

update Association
set AssocSecondaryPhone = dbo.formatphonenumber(AssocSecondaryPhone)

update Association
set AssocFax = dbo.formatphonenumber(AssocFax)

update EmergencyContact
set EmergencyPrimaryPhone = dbo.formatphonenumber(EmergencyPrimaryPhone)

update EmergencyContact
set EmergencySecondaryPhone = dbo.formatphonenumber(EmergencySecondaryPhone)

update ManagementCompany
set MgmtPrimaryPhone = dbo.formatphonenumber(MgmtPrimaryPhone)

update ManagementCompany
set MgmtSecondaryPhone = dbo.formatphonenumber(MgmtSecondaryPhone)

update ManagementCompany
set MgmtFax = dbo.formatphonenumber(MgmtFax)

update Owner
set Owner1PrimaryPhone = dbo.formatphonenumber(Owner1PrimaryPhone)

update Owner
set Owner1SecondaryPhone = dbo.formatphonenumber(Owner1SecondaryPhone)

update Owner
set Owner2PrimaryPhone = dbo.formatphonenumber(Owner2PrimaryPhone)

update Owner
set Owner2SecondaryPhone = dbo.formatphonenumber(Owner2SecondaryPhone)

update Renter
set RenterPhone = dbo.formatphonenumber(RenterPhone)

update Resource
set ResourcePhone = dbo.formatphonenumber(ResourcePhone)

update Resource
set ResourceFax = dbo.formatphonenumber(ResourceFax)

update Resource
set ContactPhone = dbo.formatphonenumber(ContactPhone)

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682165
Actually.... that was a text editor macro... i didn't realize there were multiple columns in the same tables:


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)

Open in new window

0
 

Author Comment

by:trondina
ID: 22682453
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22682610
500 points is the maximum question value.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question