PhoneFormat

• pzmrcd

  Default port

  Points: 1419

  More actions

  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

• noeld

  SSC Guru

  Points: 96590

  More actions

  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

• schleep

  SSChampion

  Points: 12585

  More actions

  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.

• pzmrcd

  Default port

  Points: 1419

  More actions

  January 10, 2006 at 1:44 pm

  #613925

  VARCHAR is what we r storing it as.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  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)