如何使用Replace in SQL server更新多个列？

How do I update different columns and rows across a table? I want to do something similiar to replace a string in SQL server

如何更新表中的不同列和行？我想做类似的事情来替换SQL服务器中的字符串

I want to do this but the value exists in multiple columns of the same type. The values are foreign keys varchars to an employee table. Each column represents a task, so the same employee may be assigned to several tasks in a record and those tasks will vary between records. How can I do this effectively? Basically something of a replace all accross varying columns throughout a table.

我想这样做，但值存在于同一类型的多个列中。值是employee表的外键varchars。每列代表一个任务，因此可以将同一员工分配给记录中的多个任务，这些任务将在不同记录之间变化。我怎样才能有效地做到这一点？在整个表格中，基本上可以替换所有不同的列。

Thanks for any help or advice.

感谢您的帮助或建议。

Cheers, ~ck in San Diego

干杯，〜在圣地亚哥

4 个解决方案

#1

This should do the trick:

这应该是诀窍：

UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring'),
    field2 = replace(field2, 'oldstring2', 'newstring2')

etc...

等等...

#2

For SQL Server 2005 this should do the trick:

对于SQL Server 2005，这应该做的伎俩：

http://www.mssqltips.com/tip.asp?tip=1555

Allows searching and replacing across all columns across all tables. Make sure you read the article though.

允许搜索和替换所有表中的所有列。请务必阅读该文章。

#3

The main idea is to create a SQL Update sentence, no matter how many fields has the table. It was created on SQL Server 2012, however I think it works on 2008 too.

主要思想是创建一个SQL Update语句，无论表有多少个字段。它是在SQL Server 2012上创建的，但我认为它也适用于2008。

Sample table:

样品表：

CREATE TABLE SampleTable
(
    Field1 INT,
    Field2 VARCHAR(20),
    Field3 VARCHAR(20),
    Field4 VARCHAR(100),
    Field5 DATETIME,
    Field6 NVARCHAR(10)
);

Get only varchar and nvarchar fields. Change OLD_TEXT and NEW_TEXT accord to your requirement. Change system_type_id values if you need match not only varchar and nvarchar fields.

只获取varchar和nvarchar字段。根据您的要求更改OLD_TEXT和NEW_TEXT。如果您不仅需要匹配varchar和nvarchar字段，请更改system_type_id值。

SELECT 'UPDATE dbo.SampleTable SET ' + STUFF((SELECT ', [' + name + '] =   REPLACE([' + name + '], ''OLD_TEXT'', ''NEW_TEXT'')' 
FROM sys.COLUMNS
WHERE 
    [OBJECT_ID] = OBJECT_ID('SampleTable')
    AND [is_identity] = 0 --It's not identity field
    AND [system_type_id] in (167, 231) -- varchar, nvarchar
FOR XML PATH('')), 1,1, '')

The result of the last query is:

最后一个查询的结果是：

UPDATE dbo.SampleTable SET  
    [Field2] = REPLACE([Field2], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field3] = REPLACE([Field3], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field4] = REPLACE([Field4], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field6] = REPLACE([Field6], 'OLD_TEXT', 'NEW_TEXT');

just copy the result and execute in SSMS. This snippet saves you a little time when writing the update sentence.

只需复制结果并在SSMS中执行即可。编写更新语句时，此代码段可为您节省一些时间。

Hope it helps.

希望能帮助到你。

#4

In answer to the poster's supplementary question about how to normalize this data structure. Here's how you'd do it:

回答海报关于如何规范化这种数据结构的补充问题。这是你如何做到的：

Project
-------
ProjectID
ProjectName
etc...

Employee
--------
EmployeeID
EmployeeName
etc...

Task
----
TaskID
ProjectID
EmployeeID
TaskDescription
etc...

Your current structure, where you have a bunch of Task1, Task2, etc... columns in the Project table, was clearly not designed by somebody that understands relational databases.

你当前的结构，你在项目表中有一堆Task1，Task2等...列显然不是由理解关系数据库的人设计的。

During the process of firing that individual, you might explain that his design violates the First Normal Form, while directing him to the "Repeating groups across columns" section of that linked article.

在解雇该个人的过程中，您可能会解释他的设计违反了第一范式，同时将他引导到该链接文章的“重复组跨栏”部分。

#1