Taming Strings

• sdryan

  SSC Enthusiast

  Points: 127

  More actions

  July 10, 2001 at 2:22 pm

  #157500

  Hi Steve

  Can you show me how to CONVERT Phone Number int(10) to nvchar (14)

  ie: 7039225903 to (703) 922-5903. Thank you.

  S Ryan

  steve ryan

  
  steve ryan

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737899

  More actions

  July 10, 2001 at 3:43 pm

  #417004

  select '(' + substring( phone, 1, 3) + ') ' + substring( phone, 4, 3) + '-' + substring(phone, 7, 4)

  Steve Jones

  steve@dkranch.net

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  July 10, 2001 at 10:23 pm

  #417020

  Couple questions. How do you get an int to hold that value? The max value is 2,147,483,648 (or (214) 748-3648 as a phone number!) Another question is why did you store it as a number?

  Also, I think Steve left out a step - have to convert it to a string somewhere, either inline or before as I've done here. (NOTE: I suspect this was a subtle trap Steve slyly planted to see if we are paying attention)

  declare @Phone bigint

  declare @NewPhone nvarchar(10)

  set @Phone=7039225903

  set @NewPhone=convert(nvarchar(12), @Phone)

  select '(' + substring(@newphone, 1, 3) + ') ' + substring(@newphone, 4, 3) + '-' + substring(@newphone, 7, 4)

  Andy

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter