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.
Advertisement
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