SQL Query with REGEXP to change URL strings dynamically

My DB table named “post” does look as follows

id   |   message
----------------
1    |   test
2    |   Here is your image link: [LINK]https://example.com/images/1234[/LINK] You can view it now.
3    |   some strings
4    |   Here is your image link: [LINK]https://example.com/images/5678[/LINK] You can view it now.
5    |   [LINK]no correct url[/LINK]
6    |   [LINK][IMG]https://example.com/images/9123[/IMG][/LINK]
7    |   [LINK]https://example.com/images/912364[/LINK]
8    |   [LINK]Some text https://example.com/images/23456 Text again[/LINK]
9    |   [URL="https://example.com/images/10796"]

So not every message row does contain an url and not every message with a [LINK]-tag does contain a proper url. Also there are enrties which have a longer ID, they should not be changed.

Now i have to change every entry which has an ID length between 4 and 5 characters:

https://example.com/images/1234
https://example.com/images/5678

To that format -> adding a file extension

https://example.com/images/1234.png
https://example.com/images/5678.png

So the “ID” is equal to the filename. Replacing just the URL isn’t that hard, but i have to add the static file extension, which is in my case “.png” at the end of the URL string.

Edit//

At the end, my DB table should look like that

id   |   message
----------------
1    |   test
2    |   Here is your image link: [LINK]https://example.com/images/1234.png[/LINK] You can view it now.
3    |   some strings
4    |   Here is your image link: [LINK]https://example.com/images/5678.png[/LINK] You can view it now.
5    |   [LINK]no correct url[/LINK]
6    |   [LINK][IMG]https://example.com/images/9123.png[/IMG][/LINK]
7    |   [LINK]https://example.com/images/912364[/LINK]
8    |   [LINK]Some text https://example.com/images/23456.png Text again[/LINK]
9    |   [URL="https://example.com/images/10796.png"]

That adding the file extension to the URL only where the “URL-ID” has 4 or 5 digits and only if the URL matches.

I’m absolutely no experienced SQL user.

Answer

You can use Regular expressions in MySQL, from 8.0 and on:

SELECT message AS original, REGEXP_REPLACE(message, '((http://|https://).*/images/[0-9]+)', '$1.png') AS new
  FROM Post 
  WHERE message REGEXP '.*(http://|https://).*/images/([0-9]{4,5})(?![0-9]).*'

The where clause simply finds matches where https:// or 'http:// is found, followed by any characters followed by /images/, followed by any number, 4 or 5 times, followed by a [, or a letter or space then any characters.

The (?![0-9]) is important as the .* will match any characters, including numbers. So without it, matches with 6+ numbers would be found. It basically means “Anything but a number”.

The regexp is using a capture group to capture everything before the numbers, and replace it with itself, plus the .png.

Results given your example:

Here is your image link: [LINK]https://example.com/images/1234.png[/LINK] You can view it now.
Here is your image link: [LINK]https://example.com/images/5678.png[/LINK] You can view it now.
[LINK][IMG]https://example.com/images/9123.png[/IMG][/LINK]
[LINK]Some text https://example.com/images/23456.png Text again[/LINK]
[URL="https://example.com/images/10796.png"]

DBFiddle

https://www.db-fiddle.com/#&togetherjs=4qC2I51yju

Edit: The above won’t work in MariaDB

Becuase you are using MariaDB 10, Taking a look at the reference for the REGEXP_REPLACE function: https://mariadb.com/kb/en/regexp_replace/

You need to use \1, not $1. So replace $1.png with \1.png if you are using MariaDB.

Final query used to update:

UPDATE Post SET message = REGEXP_REPLACE(message, '((http://|https://).*/images/[0-9]+)', '$1.png')
      WHERE message REGEXP '.*(http://|https://).*/images/([0-9]{4,5})(?!0-9]).*';

Change $1.png to \1.png for MariaDB