如何替换SQL Server表列中的字符串

I have a table (SQL Sever) which references paths (UNC or 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.

在path列中，我有许多记录，我只需要更改路径的一部分，而不需要更改整个路径。我需要在每条记录中把相同的字符串换成新的。

How can I do this with a simple update?

我如何通过一个简单的更新来做到这一点?

9 个解决方案

#1

495

It's this easy:

这个容易:

update my_table
set path = replace(path, 'oldstring', 'newstring')

#2

110

UPDATE [table]
SET [column] = REPLACE([column], '/foo/', '/bar/')

#3

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'

#4

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 CAST function I got an error

没有CAST函数，我得到一个错误

Argument data type ntext is invalid for argument 1 of replace function.

参数数据类型ntext对于替换函数的参数1无效。

#5

You can use this query

您可以使用这个查询

update table_name set column_name = replace (column_name , 'oldstring' ,'newstring') where column_name like 'oldstring%'

#6

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之类的文本，我们需要将列值转换为string，然后转换为:

update URL_TABLE
set Parameters = REPLACE ( cast(Parameters as varchar(max)), 'india', 'bharat')
where URL_ID='150721_013359670'

#7

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”中的temporery列名“~/”是我当前的字符串。(旧的字符串)“. ./“是我要的字符串。”(新字符串)“tblMyTable”是我在数据库中的表。

#8

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 table but after executing replace function it will be this value from table but updated

初始值是表中的这个值，但执行replace函数后，它将是表中的这个值，但会更新

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

希望这能给你更好的视觉化

#9

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

#1

495