Post reply

PhoneFormat

  • January 10, 2006 at 12:58 pm

    #364636

    Hi

    With in a stpred proc I want to format a phone number which is an input param.

    If PhoneParam is 2223334444 I would like to format it as (222)333-4444 and if it is 12223334444 I want to change it to 1(222)333-4444.

    Is there a sample function to help such conversion.

    Thanks

  • January 10, 2006 at 1:25 pm

    #613919

    are those the only possible combinations?

    What if a number is missing?

    What if the country code is more than one digit, ex: UK = 44?

    ...

     


    * Noel

  • January 10, 2006 at 1:30 pm

    #613920

    How are you storing it? INT or VARCHAR?

    If you're storing INT, then do nothing: it's a presentation issue, and you can format it on retrieval. (This is what we've done for North American Numbering Plan (NANP) numbers -- actually, area code in it's own INT field, and the number in another INT.)

    For other countries, there appears to be neither rhyme nor reason, so those numbers are stored as VARCHAR.

    If you're storing VARCHAR, DATALENGTH or SUBSTRING will tell you which way to go.

  • January 10, 2006 at 1:44 pm

    #613925

    VARCHAR is what we r storing it as.

  • January 10, 2006 at 5:37 pm

    #613957

    There's a couple hundred different ways of doing this... here's one...

    --For NANPA numbers only...

     CREATE TABLE #example (ID INT IDENTITY(1,1), PhoneNum VARCHAR(15))

     INSERT INTO #example (PhoneNum)

     SELECT '2223334444' UNION ALL

     SELECT '12223334444'

     SELECT CASE LEFT(PhoneNum,1)

                 WHEN 1 THEN STUFF(STUFF(STUFF(PhoneNum,8,0,'-'),5,0,')'),2,0,'(')

                 ELSE STUFF(STUFF(STUFF(PhoneNum,7,0,'-'),4,0,')'),1,0,'(')

            END  AS FormattedPhoneNum

    FROM #example

    --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)
    Intro to Tally Tables and Functions

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

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