Format Phone Number function

  • Comments posted to this topic are about the item Format Phone Number function

  • This code seems like a very convoluted way to parse a 10 digit number and put parenthesis, spaces and dashes in the result.

    Why not check for numeric and if true use the substr command to create the result?

  • Kinda need to agree with Jay on this one. Even though it is functional, it is not something I would be using for my applications.

    For my usage, I would probably have used a RegEx on the client side to strip out the non-numerics, and used a client side format function as well.

    Phone numbers are a tricky item to format, as the ISO standard is poorly written IMHO; and, different areas of the world have different rules which need to be applied when dialing.

    Director of Transmogrification Services
  • Someone recently posted a function like the one below that I have modified to validate US/Canadian format phone numbers. Sorry that I can't remember the OP to give proper credit. But this works well for me. Obviously, it would have to be modified to handle international formats.

    /*

    SELECT dbo.ufnFormatUSPhone('7044567890')-- Valid Number

    SELECT dbo.ufnFormatUSPhone('17044567890')-- Valid Number without initial '1'

    SELECT dbo.ufnFormatUSPhone('4567890')-- Valid Number without area code

    SELECT dbo.ufnFormatUSPhone('24567890')-- Returns NULL

    SELECT dbo.ufnFormatUSPhone('123456789099999')-- Returns NULL

    SELECT dbo.ufnFormatUSPhone('abc')-- Returns NULL

    */

    ALTER FUNCTION [dbo].[ufnFormatUSPhone]

    (

    @phonenumber VARCHAR(20)

    )

    RETURNS VARCHAR(20)

    AS

    BEGIN

    /** Remove White Space and non-Integer(s) values **/

    WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) > 0

    BEGIN

    SET @phonenumber = REPLACE(@phonenumber, SUBSTRING(@phonenumber, PATINDEX('%[^0-9]%', @phonenumber),1), '')

    END

    SET @phonenumber = LTRIM(RTRIM(@phonenumber))

    /** Get the number of digits **/

    DECLARE @NumChars INT

    SET @NumChars = LEN(@phonenumber)

    /** Check to see if this number has a leading value of '1' **/

    IF @NumChars = 11 AND LEFT(@phonenumber,1) = '1'

    BEGIN

    SET @phonenumber = RIGHT(@phonenumber,10)

    SET @NumChars = LEN(@phonenumber)

    END

    /** If valid number of digits proceed with formatting **/

    IF @NumChars = 7 OR @NumChars = 10

    BEGIN

    SET @phonenumber = REVERSE(@phonenumber)

    /** Format Phone Number **/

    SET @phonenumber =

    REVERSE(LEFT(@phonenumber,4)

    + '-'

    + SUBSTRING(@phonenumber,5,3)

    + COALESCE(' )'

    + NULLIF(SUBSTRING(@phonenumber,8,3),'') + '(', ''))

    END

    ELSE

    SET @phonenumber = NULL

    RETURN @phonenumber

    END

  • I think it is better to write CLR function for it,if you are using sql server 2005 or 2008

  • The value of Out parameter is not coming.

    Thanks

  • Bharat Panthee (12/8/2010)


    I think it is better to write CLR function for it,if you are using sql server 2005 or 2008

    This is why I don't allow people to write CLRs on my servers. This isn't rocket science nor a performance problem for SQL Server.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Nice work

    Steven Willis (12/7/2010)


    Someone recently posted a function like the one below that I have modified to validate US/Canadian format phone numbers. Sorry that I can't remember the OP to give proper credit. But this works well for me. Obviously, it would have to be modified to handle international formats.

    /*

    SELECT dbo.ufnFormatUSPhone('7044567890')-- Valid Number

    SELECT dbo.ufnFormatUSPhone('17044567890')-- Valid Number without initial '1'

    SELECT dbo.ufnFormatUSPhone('4567890')-- Valid Number without area code

    SELECT dbo.ufnFormatUSPhone('24567890')-- Returns NULL

    SELECT dbo.ufnFormatUSPhone('123456789099999')-- Returns NULL

    SELECT dbo.ufnFormatUSPhone('abc')-- Returns NULL

    */

    ALTER FUNCTION [dbo].[ufnFormatUSPhone]

    (

    @phonenumber VARCHAR(20)

    )

    RETURNS VARCHAR(20)

    AS

    BEGIN

    /** Remove White Space and non-Integer(s) values **/

    WHILE PATINDEX('%[^0-9]%', LTRIM(RTRIM(@phonenumber))) > 0

    BEGIN

    SET @phonenumber = REPLACE(@phonenumber, SUBSTRING(@phonenumber, PATINDEX('%[^0-9]%', @phonenumber),1), '')

    END

    SET @phonenumber = LTRIM(RTRIM(@phonenumber))

    /** Get the number of digits **/

    DECLARE @NumChars INT

    SET @NumChars = LEN(@phonenumber)

    /** Check to see if this number has a leading value of '1' **/

    IF @NumChars = 11 AND LEFT(@phonenumber,1) = '1'

    BEGIN

    SET @phonenumber = RIGHT(@phonenumber,10)

    SET @NumChars = LEN(@phonenumber)

    END

    /** If valid number of digits proceed with formatting **/

    IF @NumChars = 7 OR @NumChars = 10

    BEGIN

    SET @phonenumber = REVERSE(@phonenumber)

    /** Format Phone Number **/

    SET @phonenumber =

    REVERSE(LEFT(@phonenumber,4)

    + '-'

    + SUBSTRING(@phonenumber,5,3)

    + COALESCE(' )'

    + NULLIF(SUBSTRING(@phonenumber,8,3),'') + '(', ''))

    END

    ELSE

    SET @phonenumber = NULL

    RETURN @phonenumber

    END

Viewing 8 posts - 1 through 7 (of 7 total)

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