Cleansing Telephone Numbers in SQL Server

One of the most common challenges we face on client infrastructure is badly formatted telephone numbers. They tend to get into a bad condition when they have been run through Excel or various other scenarios changing the output.

This function is targeted at UK numbers with some scope for lengths of international numbers. It could be adapted for any other region quite easily by adapting the first 6 case items in the last case statement, and the three case items in the second last case statement.

This has been in use for a number of years, and has salvaged countless telephone numbers using the following steps;

• Backup the number into a temporary variable
  
• Replace a starting sign with double zero
• Remove any remaining text characters from the string
• Replace UK dial-in numbers with 0
• Check the length of each number type.
• Return the new number or return the old number if it couldn't be validated.