mysql - 仅使用 Mysql 替换替换中所需的匹配项

Question

使用函数替换

Replace(FieldX,'FindString','ReplaceString') where FieldX = 'ABC'

工作正常，直到字符串中有一个我不想替换的额外匹配项。

就我而言，我有一个全部大写的地址字段。但是，当出现以下情况时，我想将“PR”更改为“Prairie”:

• “PR %”
• “% PR”
• % PR %'

但是如果我这样做:

Update TableA 
Set Address=Replace(Address,'PR','PRAIRIE')
where Address like '%PR ' or Address like 'PR %' or Address like '% PR '

然后'PR PRIMO'变成'PRAIRIE PAIRIEIMP'

我想，尽管考虑到我的更改程度，它会变得很麻烦，但我可以通过三个查询来解决这个问题

 Update TableA 
 Set Address=Replace(Address,'PR ','PRAIRIE ')
 where Address like  like 'PR %'

 Update TableA 
 Set Address=Replace(Address,' PR',' PRAIRIE')
 where Address like  like '% PR'

 Update TableA 
 Set Address=Replace(Address,' PR ',' PRAIRIE ')
 where Address like  like '% PR %'

但这会很麻烦(我还有更多的替换要做和其他问题)，而且似乎它仍然会产生我没有预料到的错误。替换表也非常大，这使处理时间增加了三倍。

有没有人遇到过一种不太严厉的方法来解决这个问题？如果这是正则表达式，我想我可以逃脱它，但我发现正则表达式为这种类型的替换增加了巨大的开销，正如我所说的表很大。

Answer 1

你可以通过用两个空格包裹所有内容然后替换它来做到这一点(我认为)。 (如果使用正则表达式而不影响单词中的 pr，这会处理 ^PR 和 PR$ 情况，因为前后永远不会有空格。使用修剪作为删除空格的最后一步:

mysql> SELECT TRIM(REPLACE(' PR PRIMO ', ' PR ', ' PRAIRIE '));
+--------------------------------------------------+
| TRIM(REPLACE(' PR PRIMO ', ' PR ', ' PRAIRIE ')) |
+--------------------------------------------------+
| PRAIRIE PRIMO                                    |
+--------------------------------------------------+
1 row in set (0.00 sec)

请注意，如果在大型表上使用大量替换，则使用表来协调更新应该可以节省大量时间。下面是一个示例，其中在更新中通过 concat 添加和删除空格，允许您仅将正常值添加到替换表中。

代码:

DROP TABLE IF EXISTS hugeTable;
CREATE TABLE hugeTable(address CHAR(32));

DROP TABLE IF EXISTS replacements;
CREATE TABLE replacements(find CHAR(8), `replace` CHAR(8));

INSERT INTO hugeTable VALUES ('PR PRIMO');

INSERT INTO replacements VALUES ('PR', 'PRAIRIE');

SELECT * FROM hugeTable;

UPDATE hugeTable A, replacements B
SET A.address = TRIM(REPLACE(CONCAT(' ', A.address, ' '), CONCAT(' ', B.find, ' '), CONCAT(' ', B.`replace`, ' ')));

SELECT * FROM hugeTable;

查询:

mysql> CREATE TABLE hugeTable(address CHAR(32));
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> DROP TABLE IF EXISTS replacements;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE replacements(find CHAR(8), `replace` CHAR(8));
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO hugeTable VALUES ('PR PRIMO');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> INSERT INTO replacements VALUES ('PR', 'PRAIRIE');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT * FROM hugeTable;
+----------+
| address  |
+----------+
| PR PRIMO |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE hugeTable A, replacements B
    -> SET A.address = TRIM(REPLACE(CONCAT(' ', A.address, ' '), CONCAT(' ', B.find, ' '), CONCAT(' ', B.`replace`, ' ')));
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT * FROM hugeTable;
+---------------+
| address       |
+---------------+
| PRAIRIE PRIMO |
+---------------+
1 row in set (0.00 sec)

问候，

詹姆斯