I have a table value with multiple similar blocks on code I'm trying to do a search and replace on for a "src" value. The only value that differentiates these blocks is the "id". I'm stumped on how to construct the mysql statement. Here is the 'meta_value' cell content:

[av_image src='https://example.com/wp-content/uploads/2022/09/replace_this.jpg' attachment='1252' align='center' id=''][/av_image]

[av_image src='http://example.com/wp-content/uploads/2022/09/dont_replace_this.jpg' attachment='560' align='center' id='hero_img'][/av_image]

I have the following statement, but it replaces BOTH src strings.

UPDATE wp_postmeta SET meta_value = REGEXP_REPLACE(meta_value, "src='[^']*'", "src='NEWURL'") WHERE post_id = 2 AND meta_key = '_aviaLayoutBuilderCleanData'

How do I modify the pattern in REGEXP_REPLACE to only replace the src in the [av_image] block with the id='hero_img'?

CodePudding user response：

Add a Positive Lookahead to match only the string with hero_img in it:

(?=.*hero_img) 

Combine it with what you have:

src='[^']*'(?=.*hero_img)  

Regex Demo

UPDATE wp_postmeta 
  SET meta_value = REGEXP_REPLACE(meta_value, "src='[^']*'(?=.*hero_img)", "src='NEWURL'") 
WHERE post_id = 2 AND meta_key = '_aviaLayoutBuilderCleanData'

Fiddle Demo.

Result:

Regex Lookahead Tutorial