blocks|key|1353437|text|这很简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1353438|update+my_table
set+path+=+replace(path,+'oldstring',+'newstring')|code-block|syntax|javascript|1353439|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It's this easy:

<pre><code>update my_table
set path = replace(path, 'oldstring', 'newstring')
</code></pre>

blocks|key|1568637|text|UPDATE+[table]
SET+[column]+=+REPLACE([column],+'/foo/',+'/bar/')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1568638|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>UPDATE [table]
SET [column] = REPLACE([column], '/foo/', '/bar/')
</code></pre>

blocks|key|1353496|text|我尝试了上面的方法，但没有得到正确的结果。下面的代码执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1353497|update+table
set+path+=+replace(path,+'oldstring',+'newstring')+where+path+=+'oldstring'|code-block|syntax|javascript|1353498|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I tried the above but it did not yield the correct result. The following one does:

<pre><code>update table
set path = replace(path, 'oldstring', 'newstring') where path = 'oldstring'
</code></pre>

blocks|key|1353541|text|UPDATE+CustomReports_Ta
SET+vchFilter+=+REPLACE(CAST(vchFilter+AS+nvarchar(max)),+'\\Ingl-report\Templates',+'C:\Customer_Templates')
where+CAST(vchFilter+AS+nvarchar(max))+LIKE+'%25\\Ingl-report\Templates%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1353542|如果不使用CAST函数，我会得到一个错误|unstyled|offset|length|style|CODE|1353543|参数数据类型ntext对于replace函数的参数%251无效。|1353544|entityMap^0|0|5|4|0|6|5|D|7|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@$G|T|H|U|I|J]|$G|V|H|W|I|J]]|9|@]|A|$]]|$1|M|3|-4|5|F|7|X|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>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%'
</code></pre>

Without the <code>CAST</code> function I got an error 

Argument data type <code>ntext</code> is invalid for argument 1 of <code>replace</code> function.

blocks|key|1568843|text|您可以使用此查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1568844|update+table_name+set+column_name+=+replace+(column_name+,+'oldstring'+,'newstring')+where+column_name+like+'oldstring%25'|code-block|syntax|javascript|1568845|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use this query 

<pre><code>update table_name set column_name = replace (column_name , 'oldstring' ,'newstring') where column_name like 'oldstring%'
</code></pre>

blocks|key|1353678|text|所有的答案都很棒，但我只想给你一个很好的例子|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1353679|select+replace('this+value+from+table',+'table',++'table+but+updated')|code-block|syntax|javascript|1353680|此SQL语句将用第三个参数替换给定语句(第一个参数)中存在的单词"table“(第二个参数|1353681|初始值是this+value+from+table，但是在执行replace函数之后，它将是this+value+from+table+but+updated|offset|length|style|CODE|1353682|这是一个真实的例子|1353683|UPDATE+publication
SET+doi+=+replace(doi,+'10.7440/perifrasis',+'10.25025/perifrasis')
WHERE+doi+like+'10.7440/perifrasis%25'|1353684|例如，如果我们有这个值|1353685|10.7440/perifrasis.2010.1.issue-1|1353686|它将会成为|1353687|10.25025/perifrasis.2010.1.issue-1|1353688|希望这篇文章能给你更好的视觉效果|1353689|entityMap^0|0|0|0|4|L|1A|X|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|16|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|17|8|@$K|18|L|19|M|N]|$K|1A|L|1B|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1I|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|13|$]]

all answers are great but I just want to give you a good example 

<pre><code>select replace('this value from table', 'table', 'table but updated')
</code></pre>

this SQL statement will replace the existence of the word "table"
(second parameter) inside the given statement(first parameter) with the third parameter 

the initial value is <code>this value from table</code> but after executing replace function it will be <code>this value from table but updated</code>

and here is a real example 

<pre><code>UPDATE publication
SET doi = replace(doi, '10.7440/perifrasis', '10.25025/perifrasis')
WHERE doi like '10.7440/perifrasis%'
</code></pre>

for example if we have this value

<pre><code>10.7440/perifrasis.2010.1.issue-1
</code></pre>

it will become

<pre><code>10.25025/perifrasis.2010.1.issue-1
</code></pre>

hope this gives you better visualization

blocks|key|1568966|text|您需要借助replace函数来替换path。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1568967|update+table_name+set+column_name+=+replace(column_name,+'oldstring',+'newstring')|code-block|syntax|javascript|1568968|这里的column_name指的是您想要更改的列。|offset|length|style|CODE|1568969|希望它能起作用。|1568970|entityMap^0|0|0|3|B|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

you need to replace path with the help of replace function.

<pre><code>update table_name set column_name = replace(column_name, 'oldstring', 'newstring')
</code></pre>

here <code>column_name</code> refers to that column which you want to change.

Hope it will work.

blocks|key|1353606|text|您还可以在运行时替换电子邮件模板的大文本，这里有一个简单的示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1353607|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|code-block|syntax|javascript|1353608|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You also can replace large text for email template at run time, here is an simple example for that.

<pre class="lang-js prettyprint-override"><code>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
</code></pre>

I have a table (<code>SQL Sever</code>) which references paths (<code>UNC</code> or otherwise), but now the path is going to change. 

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 <code>update</code>?

How to replace a string in a SQL Server Table Column

我有一个引用路径(UNC或其他路径)的表(SQL Sever)，但是现在路径要更改了。在path列中，我有许多记录，我只需要更改路径的一部分，而不是整个路径。并且我需要在每个记录中将相同的字符串更改为新的字符串。我如何用一个简单的update来做到这一点

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

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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