add prefix to numeric values

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  June 26, 2019 at 8:35 pm

  #3657912

  I have a column named TPN which is a varchar type.  90% of the data are numeric values 5-9 in length.  In some rows where there is no numeric values, some rows may have character values i.e. "TPN" , "TBC", NULL, empty string value

  I want to pad all numeric values with prefix of zeros where it has numeric values less than 9 digits.

  How can update my data to do this?

   

  • This topic was modified 5 years, 9 months ago by  GrassHopper.

• ThatDataGuy

  SSC Rookie

  Points: 33

  More actions

  June 26, 2019 at 9:51 pm

  #3657925

  update {your-table}
     set TPN = right('000000000' + rtrim(ltrim(TPN)), 9)
   where isnumeric(TPN) > 0
  

• Chris Wooding

  SSCarpal Tunnel

  Points: 4580

  More actions

  July 3, 2019 at 12:33 pm

  #3659733

  If you want to be stricter on what should be interpreted as numeric (and are using 2012 or later), you could change the WHERE clause to

  where try_cast(TPN as int) is not null

• ScottPletcher

  SSC Guru

  Points: 101180

  More actions

  July 3, 2019 at 1:19 pm

  #3659753

  UPDATE dbo.table_name
  SET TPN = RIGHT(REPLICATE('0', 8) + CAST(TPN AS varchar(9)), 9)
  WHERE TPN NOT LIKE '%[^0-9]%'

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  July 5, 2019 at 11:25 am

  #3660167

  Thanks!  I forgot about Try_cast .... Need to start using the new functions.