Dev Dev - 23 days ago 12
SQL Question

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 number field. The desired output is 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 however it is not working.

Can anyone help me regarding this?

Thank you..

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

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

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