Remove first two digit and change phonenumber format SQL

• hussain_ahmad73

  SSC Veteran

  Points: 276

  More actions

  June 9, 2016 at 8:58 pm

  #326868

  hi,

  I have a tableA in which PhoneNumber column have values like 911234567891.

  i want to remove the first two digits from the start i.e 1234567891 and then i want to change it to phone number format xxx-xxx-xxxx so the ending result will be 123-456-7891.

  I have already done it using seprate Select and Update query like this:

  SELECT

  [Phone Numbers]

  ,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]

  FROM tbl_sample

  UPDATE tbl_sample

  SET [Phone Numbers] = SUBSTRING([Phone Numbers], 3, 8000)

  WHERE [Phone Numbers] LIKE '91%'

  Is it possible to do it using single select query only so that there will be no need to update the data first?

  Any kind of help or suggestion will be appreciated.

• subramanian.esakkimuthu 40485

  SSCrazy

  Points: 2022

  More actions

  June 10, 2016 at 12:35 am

  #1884286

  You can use the following query to format the phone number.

  select [Phone Number],stuff(stuff(SUBSTRING([phone number],3,10),4,0,'-'),8,0,'-') [Formated Phone Number] from tbl_sample

  if you need any more help let me know