如何使用 mySQL replace() 替换多条记录中的字符串？

How can I use mySQL replace() to replace strings in multiple records?

提问by EmmyS

We have a database that has a bunch of records with some bad data in one column, in which an embedded editor escaped some stuff that shouldn't have been escaped and it's breaking generated links.

我们有一个数据库，其中有一堆记录，其中一列中有一些错误数据，其中嵌入式编辑器转义了一些不应该转义的内容，并且破坏了生成的链接。

I want to run a query to replace the bad characters in all the records, but can't figure out how to do it. I found the replace()functionin MySQL, but how can I use it inside a query?

我想运行一个查询来替换所有记录中的坏字符，但不知道该怎么做。我在 MySQL 中找到了该replace()函数，但是如何在查询中使用它？

For example, what would be the correct syntax if I wanted to replace the string <with an actual less-than angle bracket (<) in all records that have <in the articleItemcolumn? Can it be done in a single query (i.e. select and replace all in one swoop), or do I have to do multiple queries? Even if it's multiple queries, how do I use replace()to do the replace on the value of a field on more than one record?

例如，什么是正确的语法，如果我想替换字符串<与实际低于尖括号（<）中有所有的记录<在articleItem列？它可以在单个查询中完成（即一次性选择和替换所有内容），还是我必须执行多个查询？即使是多个查询，我如何使用replace()对多个记录上的字段值进行替换？

回答by Raj More

At a very generic level

在非常通用的级别

UPDATE MyTable

SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

WHERE SomeOtherColumn LIKE '%PATTERN%'

In your case you say these were escaped but since you don't specify how they were escaped, let's say they were escaped to GREATERTHAN

在您的情况下，您说这些已转义，但由于您没有指定它们是如何转义的，因此假设它们已转义为 GREATERTHAN

UPDATE MyTable

SET StringColumn = REPLACE (StringColumn, 'GREATERTHAN', '>')

WHERE articleItem LIKE '%GREATERTHAN%'

Since your query is actually going to be working inside the string, your WHEREclause doing its pattern matching is unlikely to improve any performance - it is actually going to generate more work for the server. Unless you have another WHERE clause member that is going to make this query perform better, you can simply do an update like this:

由于您的查询实际上将在字符串内工作，因此您WHERE进行模式匹配的子句不太可能提高任何性能 - 它实际上会为服务器生成更多工作。除非您有另一个 WHERE 子句成员可以使此查询执行得更好，否则您可以简单地进行如下更新：

UPDATE MyTable
SET StringColumn = REPLACE (StringColumn, 'GREATERTHAN', '>')

You can also nest multiple REPLACEcalls

您还可以嵌套多个REPLACE调用

UPDATE MyTable
SET StringColumn = REPLACE (REPLACE (StringColumn, 'GREATERTHAN', '>'), 'LESSTHAN', '<')

You can also do this when you select the data (as opposed to when you save it).

您也可以在选择数据时执行此操作（而不是在保存数据时）。

So instead of :

所以而不是：

SELECT MyURLString From MyTable

SELECT MyURLString From MyTable

You could do

你可以做

SELECT REPLACE (MyURLString, 'GREATERTHAN', '>') as MyURLString From MyTable

SELECT REPLACE (MyURLString, 'GREATERTHAN', '>') as MyURLString From MyTable

回答by simshaun

UPDATE some_table SET some_field = REPLACE(some_field, '&lt;', '<')

回答by Lead Developer

Check this

检查这个

UPDATE some_table SET some_field = REPLACE("Column Name/String", 'Search String', 'Replace String')

Eg with sample string:

例如使用示例字符串：

UPDATE some_table SET some_field = REPLACE("this is test string", 'test', 'sample')

EG with Column/Field Name:

带有列/字段名称的 EG：

UPDATE some_table SET some_field = REPLACE(columnName, 'test', 'sample')

回答by Gianluca D'Ardia

you can write a stored procedure like this:

你可以写一个这样的存储过程：

CREATE PROCEDURE sanitize_TABLE()

BEGIN

#replace space with underscore

UPDATE TableSET FieldName= REPLACE(FieldName," ","_") WHERE FieldNameis not NULL;

#delete dot

UPDATE TableSET FieldName= REPLACE(FieldName,".","") WHERE FieldNameis not NULL;

#delete (

UPDATE TableSET FieldName= REPLACE(FieldName,"(","") WHERE FieldNameis not NULL;

#delete )

UPDATE TableSET FieldName= REPLACE(FieldName,")","") WHERE FieldNameis not NULL;

#raplace or delete any char you want

#..........................

END

CREATE PROCEDURE sanitize_ TABLE()

开始

#用下划线替换空格

UPDATE TableSET FieldName= REPLACE( FieldName," ","_") WHERE FieldName不是 NULL;

#删除点

UPDATE TableSET FieldName= REPLACE( FieldName,".","") WHERE FieldName不是 NULL;

＃删除 （

UPDATE TableSET FieldName= REPLACE( FieldName,"(","") WHERE FieldNameis not NULL;

＃删除 ）

UPDATE TableSET FieldName= REPLACE( FieldName,")","") WHERE FieldName不是 NULL;

#raplace 或删除您想要的任何字符

#……………………

结尾

In this way you have modularized control over table.

通过这种方式，您可以对表格进行模块化控制。

You can also generalize stored procedure making it, parametric with table to sanitoze input parameter

您还可以概括存储过程，使其与表参数化以清理输入参数

回答by TechyFlick

This will help you.

这会帮助你。

UPDATE play_school_data SET title= REPLACE(title, "'", "'") WHERE title = "Elmer's Parade";

Result:

结果：

title = Elmer's Parade