I want to update a column of a table by inserting 0 (zero) in between exising value in sql

Question

TABLE Name: Issue_A

Key Ref_no
62939   Ng20
62940   Ng21
62944   Ng22
62946   Ng23
62949   Ng24
62951   Ng25
62952   Ng26
62953   Ng27

I want to insert 0 (zero) and update the table between 'Ng' and '20'

update Issue_A
set ref_no=REPLACE(SUBSTRING(ref_no,0, CHARINDEX('Ng', ref_no)),'H-','')))
where key in (62939,62940,62944,62946,62949,62951,62952,62953)

So the output will be 'Ng020' and 'Ng021' and so on

Answer 1

You can use the Replace() function.

 UPDATE Issue_A
      SET Ref_no = REPLACE(Ref_no, 'Ng', 'Ng0') 
 FROM Issue_A
 WHERE Key IN (62939,62940,62944,62946,62949,62951,62952,62953)