Formatting Phone Numbers

Ever wonder how to fix the format of phone and FAX numbers in MAX or Synergy? Users enter them in various formats and then they can look inconsistent and even unprofessional when they are printed out on reports or documents that are used by others.

While there is not right or wrong format and your ‘standard’ could be different, most MAX customers like a format of 123-123-1234 for the United States format. It is short and easy to read. The sample below shows a before and after view of MAX Customer data.

There is a simple function you can add to SQL that can be used to reformat the phone, fax and mobile numbers that creates this format. If you prefer other formats, it is easy to re-code the function for other standards.

The routine can be run at any desired frequency, say once a night to make sure all the numbers are properly formatted and it can be used when bulk loading data to ensure data consistency.

It can be used on the MAX Customer Master, MAX Vendor Master, Synergy Account and Synergy Address file for example.

The routine is added to the SQL ‘master’ database using the following code:

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER FUNCTION [dbo].[udf_ParsePhone](@string VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

 

BEGIN

DECLARE @count int

DECLARE @intNumbers VARCHAR(MAX)

SET @count = 0

SET @intNumbers = ”

 

WHILE @count <= LEN(@string)

BEGIN

IF SUBSTRING(@string, @count, 1)>=’0′ and SUBSTRING (@string, @count, 1) <=’9′

BEGIN

SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)

END

SET @count = @count + 1

END

RETURN SUBSTRING(@intNumbers,1,3) + ‘-‘ + SUBSTRING(@intNumbers,4,3) + ‘-‘ + SUBSTRING(@intNumbers,7,4)

END

Then the ParsePhone routine can be called to update the Customer Master Phone and FAX Numbers with code like this:

UPDATE Customer_Master

SET PHONE_23 = master.dbo.udf_ParsePhone(PHONE_23),

FAXNO_23 = master.dbo.udf_ParsePhone(FAXNO_23)

WHERE CNTRY_23 = ‘United States’

After running the update, the customer data can be viewed to show the updated like this:

SELECT     CUSTID_23, PHONE_23, master.dbo.udf_ParsePhone(PHONE_23) AS NewPhone, FAXNO_23, master.dbo.udf_ParsePhone(FAXNO_23) AS NewFAX, CNTRY_23

FROM         dbo.Customer_Master

WHERE     (CNTRY_23 = ‘United States’)