Post reply

Phone number validation UDF

  • September 27, 2010 at 11:37 am

    #229760

    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.

  • September 27, 2010 at 11:49 am

    #1227876

    Why not just remove all "(", ")" and "-" characters, similar to

    REPLACE(REPLACE(REPLACE(phone_number, '(', ''), ')', ''), '-', '')

    And then maybe do an additional check like ISNUMERIC(),

    Then if the validation checks out, insert SUBSTRING(phone_number, 1, 3) + '-' + SUBSTRING(phone_number, 4, 3) + '-' + SUBSTRING(phone_number, 7, 4)

  • September 27, 2010 at 12:55 pm

    #1227925

    If you're actually doing validation and not just adjusting formatting, you may want to be more restrictive on valid formats. Below are some possibilities to get you started with that.

    I'll assume no "words for numbers" (e.g., no "800FLOWERS", :-)).

    I'll also assume, for now, that no leading 1/0, for direct-dial/oper assisted, is present in any number.

    Then, the basic valid patterns, to me, would be:

    ten solid digits;

    ten digits, broken into 3,3,4 with various editing chars;

    ten digits, broken into 3,7 with various editing chars;

    seven solid digits;

    seven digits, broken into 3,4 with various editing chars.

    You can then write LIKE comparisons to match against each valid pattern:

    WHERE phone LIKE REPLICATE('[0-9]', 10)

    OR phone LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]'

    OR phone LIKE '_[0-9][0-9][0-9]__[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]'

    OR phone LIKE '_[0-9][0-9][0-9]_[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]'

    OR phone LIKE '_[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    OR phone LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    --...

    That will some extra CPU but will provide a more careful validation.

    Scott Pletcher, SQL Server MVP 2008-2010

  • September 27, 2010 at 2:25 pm

    #1228004

    Replace and Substring worked for me. Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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