MySQL (MariaDB 10.1) find and replace/delete string with REGEX

Question

I have a notes column for products that many contain the following (as well as other info):

<p><em><strong>Item dropped from ESP datafeed and pricing removed from site on 02-17-2017. May still be available.</strong></em></p>

Where the 02-17-2017 date my change (but will always be in that format). Can I find and delete these strings with just a database query (leaving the rest of the data that is in the column, like a MySQL REPLACE() but with regex to match the dates) or do I need to do it in PHP?

Rick James · Accepted Answer · 2017-05-25 03:08:00Z

1

WHERE notes LIKE '<p><em><strong>Item dropped from ESP datafeed and pricing removed from site on %. May still be available.</strong></em></p>'

Note that I put a % in the string.

I don't understand "delete those strings" -- Do you want to replace them with the empty string? NULL? Delete the row containing that in the notes column? Something else? I hope that the WHERE clause is sufficient to get you moving in the right direction.

Note: REGEXP is 'overkill'; LIKE suffices.

If you want to remove the date but keep the rest of the string, then won't this do?...

UPDATE tbl
    SET notes = '<p><em><strong>Item dropped from ESP datafeed and pricing removed from site on xxxxxxxxxxx. May still be available.</strong></em></p>'
    WHERE notes LIKE '<p><em><strong>Item dropped from ESP datafeed and pricing removed from site on %. May still be available.</strong></em></p>';

(Or whatever you would like to put as a placeholder.)

MariaDB-10.0.5 has these: REGEXP_REPLACE(), REGEXP_INSTR(), and REGEXP_SUBSTR(); but I don't see that they are necessary for your case.

edited May 25, 2017 at 3:08

answered May 24, 2017 at 20:20

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

EricImprint Over a year ago

I need to find that specific string and delete it, leaving the rest of the data in the column. Basically a REPLACE() but with regex to match the dates.

EricImprint Over a year ago

There is other data around this string that contains the date. So e.g. blah blah blah Item dropped from ESP datafeed and pricing removed from site on 02-17-2017. May still be available. blah blah blah and I want to remove Item dropped from ESP datafeed and pricing removed from site on 02-17-2017. May still be available. while leaving the blah blah blah and blah blah blah

Rick James Over a year ago

OK, then add % at start and end of the LIKE string: LIKE '%...on % ... %' and hope that there are not edge cases or spelling variants that invalidate this search.

EricImprint Over a year ago

Your solution doesn't remove the string. See my solution below.

EricImprint · Accepted Answer · 2017-06-14 19:19:58Z

0

I was able to do it with:

UPDATE `ii_Product` 
SET `notes`= REGEXP_REPLACE(`notes`, '\n?<p><em><strong>Item dropped from ESP datafeed and pricing removed from site on .*\. May still be available.<\/strong><\/em><\/p>\n?\n?', '')

answered Jun 14, 2017 at 19:19

EricImprint

1771 gold badge1 silver badge13 bronze badges

Collectives™ on Stack Overflow

MySQL (MariaDB 10.1) find and replace/delete string with REGEX

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related