Phone number validation UDF

  • 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.

  • 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')

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply