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

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

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