Need to remove legacy shortcodes w/ variable content from WordPress pages using MySQL

Question

I have many pages (posts) in a WP site with legacy short code that needs to be removed and cannot accomplish what I need using WordPress find/replace plug-ins - so I'm turning to MySQL queries.

The shortcodes to be removed all follow the same pattern: "[nivo... (variable content) .../nivo]

I need to remove the entire nivo shortcode and ONLY the nivo shortcode because other shortcodes exist on some pages.

I found something very close to what I think I need, and modified the obvious parameters for this particular application as follows...

UPDATE `post_content`
REPLACE(txt, SUBSTRING(txt, LOCATE('[nivo', txt), LENGTH(txt) - LOCATE('nivo]', REVERSE(txt)) - LOCATE('nivo]', txt) + 10), '')
WHERE  txt LIKE '%(%)%'

That ^^^ is accepted in the SQL query window (no stop signs) but returns a #1064 error when executed.

Ideally I would like to TEST this first on a specific post ID just to be sure it's really catching everything but I couldn't figure out how to write that into the query.

I know VERY little about MySQL (I'm a designer) but I have DB backups ready for rollback just in case.

Help would be greatly appreciated.

Answer 1

The key is that you want to use a plugin or tool that allows you to use regex (regular expressions). I have used this Php tool, you just put it in your public_html and then access the path via a web browser: https://interconnectit.com/search-and-replace-for-wordpress-databases/

Just use a little regex in the Php tool or find a find and replace plugin that allows you to use regex. It would look something like this:

\[nivo.*?/nivo\]

\ means escaping a character, since the bracket has a special meaning in regex.

. means any character

* means any number of characters

? means 0 or more

.*? together means anything or nothing

For reference (and probably a better answer): PHP - Remove Shortcodes and Content in between with Regex Pattern