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

sql, sql-server, sql-server-2008

Solution

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

Problem

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?

Original source