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:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[udf_ParsePhone](@string VARCHAR(MAX))
DECLARE @count int
DECLARE @intNumbers VARCHAR(MAX)
SET @count = 0
SET @intNumbers = ”
WHILE @count <= LEN(@string)
IF SUBSTRING(@string, @count, 1)>=’0′ and SUBSTRING (@string, @count, 1) <=’9′
SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
SET @count = @count + 1
RETURN SUBSTRING(@intNumbers,1,3) + ‘-‘ + SUBSTRING(@intNumbers,4,3) + ‘-‘ + SUBSTRING(@intNumbers,7,4)
Then the ParsePhone routine can be called to update the Customer Master Phone and FAX Numbers with code like this:
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
WHERE (CNTRY_23 = ‘United States’)