SQL Server查找并替换特定列的所有行中的特定字

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

我有一个具有列Id和数字的表TblKit。Id是int类型的主键，Number是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

我想用CH在number字段中替换所有的KIT%的行。期望的输出是这样的

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..

谢谢你！

2 个解决方案

#1

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

• SQLFiddle Demo
• SQLFiddle演示

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

或者简单地说，如果你确定没有CKIT002这样的值

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

• SQLFiddle Demo
• SQLFiddle演示

#2

You can also export the database and then use a program like notepad++ to replace words and then inmport aigain.

您还可以导出数据库，然后使用像notepad++这样的程序来替换单词，然后使用inmport aigain。