SQL Server 在 TEXT 字段中查找和替换 [英] SQL Server find and replace in TEXT field

问题描述

我在 SQL Server 2005 中有一个数据库，它是从 SQL Server 2000 启动的，并且仍在使用 TEXT 类型的字段而不是 varchar(max).

I have a database in SQL Server 2005 that was brought up from SQL Server 2000 and is still using TEXT type fields instead of varchar(max).

我需要在文本字段中查找并替换一串字符，但是我发现的所有如何执行此操作的示例似乎都不适合我.似乎 UPDATETEXT 命令要求明确设置两个参数insert_offset"和delete_length"，但我正在搜索的字符串可能会出现在文本中的任何点，甚至是同一单元格中的多个点.我对这两个参数的理解是 im 搜索的字符串总是在同一个地方，所以 insert_offset 是 UPDATETEXT 命令将开始替换文本的文本中的空格数.

I need to find and replace a string of characters in the text field but all of the examples of how to do this that I have found don't seem like they would work for me. It seems the UPDATETEXT command requires that the two parameters "insert_offset" and "delete_length" be set explicitly but the string i am searching for could show up in the text at any point or even at several points in the same cell. My understanding of these two parameters is that the string im searching for will always be in the same place, so that insert_offset is the number of spaces into the text that the UPDATETEXT command will start replacing text.

示例:需要找到:<u> 并替换为:

Example: Need to find: <u> and Replace it with: <u>

文本字段示例:

*Everyone in the room was <b>&lt;u&gt;tired&lt;/u&gt;.</b><br>Then they woke <b>&lt;u&gt;up&lt;/u&gt;.

谁能帮我解决这个问题?谢谢！

Can anyone help me out with this? THANKS!

推荐答案

我终于想通了.它被隐藏在对 jfrobishow 发表的文章的评论中.非常感谢.

I finally figured it out. It was buried in the comments to the article jfrobishow published. Thank you SO much.

以下是让我找到解决方案的完整回复:

Here is the whole response that led me to the solution:

quote:最初由 fredclown 发表

quote:Originally posted by fredclown

如果您使用 SQL 2005，您可以使用文本类型替换.你拥有的一切要做的是以下...

If you use SQL 2005 you can use replace with a text type. All you have to do is the below ...

field = replace(cast(field as varchar(max)),'string','replacement')

field = replace(cast(field as varchar(max)),'string' ,'replacement')

像馅饼一样简单.

向弗雷德小丑竖起两个大拇指！！！命令工作对我来说也是一种魅力.这个是我写的更新语句在文本字段中查找和替换SQL Server 2005 数据库

Two thumbs up to Fredclown!!! command work like a charm for me as well. This is what I wrote my Update statement to Find and Replace in a Text field in SQL server 2005 database

UPDATE TableName SET DBTextField = REPLACE(CAST(DBTextField AS varchar(MAX))
                                               ,'SearchText', 'ReplaceText')
FROM TableName
WHERE CHARINDEX('SearchText',CAST(DBTextField as varchar(MAX)))>0

注意:这可能会截断你 dbfield 的大小，但如果是一个长文本列，让它nvarchar(max)和你不应该得到任何截断！

Note:that this may truncate the size of you dbfield , but if is a long text column make it nvarchar(max) and you should not get none truncation!

这篇关于SQL Server 在 TEXT 字段中查找和替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！