Phone number validation UDF

• PSB

  SSChampion

  Points: 13195

  More actions

  September 27, 2010 at 11:33 am

  #227393

  Hi,

  I have to create a UDF for validating phone numbers from a staging table to another table with the following pattern 713-666-7777

  If the Staging table contains Phone numbers of the following format given below then,

  Staging Table Main Table

  7132998206 --> 713-299-8206

  (713)299-8206 --> 713-299-8206

  (291)-262-2223 --> 291-262-2233

  713/2998206 --> 713-299-8206

  555-666-9999 --> do nothing . Insert as it is.

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  September 27, 2010 at 12:29 pm

  #1227912

  Basically you just want to strip all non-numeric values, and add two hyphens.

  There are a lot of variations on the function to remove non-numerics. The one I use is from: http://ssmithdev.com/2008/12/10/transact-sql-stripnonnumeric-function/

  Here is the modification to add dashes, with some validation examples at the end:

  DROP FUNCTION dbo.PhoneValidate

  GO

  CREATE FUNCTION dbo.PhoneValidate(@value AS VARCHAR(MAX)) RETURNS VARCHAR(12) AS

  BEGIN

  DECLARE @len AS INT

  DECLARE @pattern AS VARCHAR(5)

  DECLARE @result AS VARCHAR(12)

  SET @len = LEN(@Value)

  SET @pattern = '[0-9]'

  SET @result = ''

  WHILE @len> 0

  BEGIN

  SET @result = @result + CASE WHEN SUBSTRING(@value,@len,1) LIKE @pattern THEN SUBSTRING(@value,@len,1) ELSE '' END

  SET @len = @len - 1

  END

  SET @result = reverse(@result)

  SET @result = SUBSTRING(@result,1,3) + '-' + SUBSTRING(@result,4,3) + '-' + SUBSTRING(@result,7,4)

  RETURN @RESULT

  END

  GO

  SELECT dbo.PhoneValidate('(123) 855-5984')

  UNION ALL

  SELECT dbo.PhoneValidate('1234567890')

  UNION ALL

  SELECT dbo.PhoneValidate('098-765-4321')