user3649739 user3649739 - 6 months ago 9
SQL Question

Only replace desired match in replace using Mysql

Using the function replace

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


Works fine until there is an additional match inside the string that I don't want to replace.

In my case I have an address field that I got as ALL CAPS. However I want to change 'PR' to 'Prairie' when it occurs as:


  • 'PR %'

  • '% PR'

  • % PR %'



Yet if I do:

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


Then
'PR PRIMO'
becomes
'PRAIRIE PRAIRIEIMP'


I thought, even though it gets cumbersome given the extent of my changes I could solve this in three queries

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 %'


But this will be cumbersome (again I have far more replacements to do and other issues) and seems like it could still generate errors I haven't anticipated. The replace tables are also very large and this triples the processing time.

Has anyone run into a way to solve this is a less heavy-handed approach? If this were regex I could get away with it I think but I've found regex adds a huge overhead to this type of replacement and as I said the tables are large.

Answer

You can do this (I think) by wrapping everything with two spaces and then replacing that. (This takes care of the ^PR and PR$ cases if using a regex without affecting pr within words as this would never have a space before and afterwards. Use trim as a final step to remove the spaces:

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

Note that if using lots of replaces on huge tables, using a table to coordinate the update should save you significant time. Below is an example where the spaces are added and removed via concat in the update allowing you to just add normal values to the replacement table.

Code:

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;

Query:

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)

Regards,

James

Comments