如何替换 SQL Server 表列中的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/814548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace a string in a SQL Server Table Column
提问by Iralda Mitro
I have a table (SQL Sever) which references paths (UNCor otherwise), but now the path is going to change.
我有一个SQL Sever引用路径（UNC或其他方式）的表 ( ) ，但现在路径将发生变化。
In the path column, I have many records and I need to change just a portion of the path, but not the entire path. And I need to change the same string to the new one, in every record.
在路径列中，我有很多记录，我只需要更改路径的一部分，而不是整个路径。我需要在每条记录中将相同的字符串更改为新的字符串。
How can I do this with a simple update?
我怎样才能用一个简单的方法做到这一点update？
回答by cjk
It's this easy:
就这么简单：
update my_table
set path = replace(path, 'oldstring', 'newstring')
回答by Marc Gravell
UPDATE [table]
SET [column] = REPLACE([column], '/foo/', '/bar/')
回答by Caesar
I tried the above but it did not yield the correct result. The following one does:
我尝试了上述方法，但没有产生正确的结果。下面一个做：
update table
set path = replace(path, 'oldstring', 'newstring') where path = 'oldstring'
回答by Igor Bakay
UPDATE CustomReports_Ta
SET vchFilter = REPLACE(CAST(vchFilter AS nvarchar(max)), '\Ingl-report\Templates', 'C:\Customer_Templates')
where CAST(vchFilter AS nvarchar(max)) LIKE '%\Ingl-report\Templates%'
Without the CASTfunction I got an error
没有这个CAST功能，我得到了一个错误
Argument data type ntextis invalid for argument 1 of replacefunction.
参数数据类型ntext对于函数的参数 1 无效replace。
回答by Nitika Chopra
You can use this query
您可以使用此查询
update table_name set column_name = replace (column_name , 'oldstring' ,'newstring') where column_name like 'oldstring%'
回答by Basheer AL-MOMANI
all answers are great but I just want to give you a good example
所有的答案都很好，但我只想给你一个很好的例子
select replace('this value from table', 'table', 'table but updated')
this SQL statement will replace the existence of the word "table" (second parameter) inside the given statement(first parameter) with the third parameter
此 SQL 语句将用第三个参数替换给定语句（第一个参数）中存在的单词“table”（第二个参数）
the initial value is this value from tablebut after executing replace function it will be this value from table but updated
初始值是，this value from table但执行替换功能后，它将是this value from table but updated
and here is a real example
这是一个真实的例子
UPDATE publication
SET doi = replace(doi, '10.7440/perifrasis', '10.25025/perifrasis')
WHERE doi like '10.7440/perifrasis%'
for example if we have this value
例如，如果我们有这个值
10.7440/perifrasis.2010.1.issue-1
it will become
它会变成
10.25025/perifrasis.2010.1.issue-1
hope this gives you better visualization
希望这能给你更好的可视化
回答by Durgesh Pandey
select replace(ImagePath, '~/', '../') as NewImagePath from tblMyTable
where "ImagePath" is my column Name.
"NewImagePath" is temporery column Name insted of "ImagePath"
"~/" is my current string.(old string)
"../" is my requried string.(new string)
"tblMyTable" is my table in database.
其中“ImagePath”是我的列名称。
“NewImagePath”是“ImagePath”的临时列名称
“~/”是我当前的字符串。（旧字符串）
“../”是我需要的字符串。（新字符串）
“tblMyTable”是我在数据库中的表。
回答by khichar.anil
If target column type is other than varchar/nvarchar like text, we need to cast the column value as string and then convert it as:
如果目标列类型不是 varchar/nvarchar 之类的text，我们需要将列值转换为字符串，然后将其转换为：
update URL_TABLE
set Parameters = REPLACE ( cast(Parameters as varchar(max)), 'india', 'bharat')
where URL_ID='150721_013359670'
回答by khichar.anil
you need to replace path with the help of replace function.
您需要在替换功能的帮助下替换路径。
update table_name set column_name = replace(column_name, 'oldstring', 'newstring')
here column_namerefers to that column which you want to change.
这里column_name指的是您要更改的那一列。
Hope it will work.
希望它会起作用。
回答by Shekhar Patel
You also can replace large text for email template at run time, here is an simple example for that.
您还可以在运行时替换电子邮件模板的大文本，这是一个简单的示例。
DECLARE @xml NVARCHAR(MAX)
SET @xml = CAST((SELECT [column] AS 'td','',
,[StartDate] AS 'td'
FROM [table]
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
select REPLACE((EmailTemplate), '[@xml]', @xml) as Newtemplate
FROM [dbo].[template] where id = 1
