替换数据库文本字段中所有出现的子字符串 [英] Replace all occurrences of a substring in a database text field

问题描述

我有一个大约有1万条记录的数据库，其中一些包含我想替换的HTML字符。

I have a database that has around 10k records and some of them contain HTML characters which I would like to replace.

例如，我可以找到所有出现的记录： / p>

For example I can find all occurrences:

SELECT * FROM TABLE
WHERE TEXTFIELD LIKE '%&#47%'

原始字符串示例：

这是包含&＃47

如何替换所有&＃47 与 / ？

最终结果应为：

这是包含/

推荐答案

如果要用另一个字符串替换特定字符串或对该字符串进行转换，则可以在postgresql中使用替换功能。例如，要将 myfield列中所有出现的 cat替换为 dog，您可以这样做：

If you want to replace a specific string with another string or transformation of that string, you could use the "replace" function in postgresql. For instance, to replace all occurances of "cat" with "dog" in the column "myfield", you would do:

UPDATE tablename
SET myfield = replace(myfield,"cat", "dog")

您可以添加WHERE子句或您认为合适的其他逻辑。

You could add a WHERE clause or any other logic as you see fit.

或者，如果您尝试转换HTML实体，ASCII字符或在各种编码方案之间进行转换， postgre也具有此功能。 Postgresql字符串函数。

Alternatively, if you are trying to convert HTML entities, ASCII characters, or between various encoding schemes, postgre has functions for that as well. Postgresql String Functions.

这篇关于替换数据库文本字段中所有出现的子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！