Cleaning and Formatting Phone Numbers
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Sybase 15 ASE: Tested
Date: 7 Oct 2011
I recently had the challenge of working with a data feed of Address/Phone Number data originating in Sybase 15 ASE. Not only was the DBMS out of my immediate comfort zone, but the application hosting the data was 15 years old, had no data input validation. Also, the data was still actively being updated and used, was not allowed to be changed by any part of the extract, and had to be extracted anew every time it was needed.
Several key pieces of data were required, including Name, Postal Code and phone/fax number. This tip deals with the steps taken to extract, clean and format phone numbers in an acceptable way for consumption by the new application.
The Sybase challenge was easily met. I remember back in the SQL 6.x days just how keen Microsoft was to move away from the legacy Sybase code into something more flexible and sustainable, and the continuous joy(?) as a DBA with the new features in each new version. each new version.
Imagine my shock to enter the Sybase universe again to be me with something familiar - kind of like visiting your old school 20 years later - change, but not much.
Fortunately, Sybase 15 now has functions, although only the scalar variety, so performing on-the-fly data cleansing and formatting during the extract process was an option.
Included here are the resulting pair of functions that I wrote to perform the task.
Some things to note:
The extract is designed for standard 10-digit North American format phone numbers with optional extension numbers of up to 5 digits
Initially, all non-numeric characters are removed
The resulting number of digits is then used to determine the validity of the number and ultimately output formatting
The new application was still undergoing design at the time this extract work was happening, so the formatting took 2 forms for future flexibility; a masked text string, and an XML fragment
XML is not a data type supported by SYBASE (yet), so the XML fragment is created the hard way.
CTEs are not supported by SYBASE (yet), so the Numbers Table technique cannot be used to clean a string
Phone Number Cleaning Function :
Phone Number Masking Function :