SQL Server find and replace specific word in all rows of specific column

Question

I have a table TblKit that has columns Id and Number. Id is primary key of type int and Number is varchar(50).

The data in the table looks like this:

Id     Number ---    ------ 1      KIT001 2      KIT002  3      DMB001 4      DM002 5      KIT003 

I want to replace all the rows of KIT% with CH in the Number field. The desired output would look like:

Id     Number ---    ------ 1      CH001 2      CH002  3      DMB001 4      DM002 5      CH003 

I have tried this update query:

UPDATE TblKit SET Number = REPLACE(Number, N'%KIT%', 'CH')  

But it is not working.

Can anyone help me regarding this?

Answer 1

UPDATE tblKit SET number = REPLACE(number, 'KIT', 'CH') WHERE number like 'KIT%' 

• SQLFiddle Demo

or simply this if you are sure that you have no values like this CKIT002

UPDATE tblKit SET number = REPLACE(number, 'KIT', 'CH') 

• SQLFiddle Demo