how to update records

• md israil hussain

  SSC Enthusiast

  Points: 153

  More actions

  July 25, 2004 at 5:46 am

  #108750

  i have recrods  e.g :0523422 we like update to 05023422 that means add '0' after 05 to all records

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 25, 2004 at 10:49 am

  #515999

  Hey MD,

  Check out if the SQL below gives the results you are looking for:

  SELECT

   code = r.code, 

   code_updated = SUBSTRING(r.code, 1, 2) + '0' + SUBSTRING(r.code, 3, LEN(r.code) - 2)

  FROM

    Records_Table r

  WHERE

    LEN(r.code) > 2 AND -- codes that have 2 or more characters

    CHARINDEX('0', r.code) = 1 -- codes that start with a '0'

  The SQL assumes that r.code is already some type of string (i.e. CHAR, VARCHAR, etc.). If not, you can CAST it like this:

  code_updated = SUBSTRING(CAST(r.code AS VARCHAR), 1, 2) + '0' + SUBSTRING(r.code, 3, LEN(CAST(r.code AS VARCHAR)) - 2)

  JP