SQL Query replace string with placeholder

Question

My table has lots of entries (image paths). Such a string is an URL and looks like that

https://example.com/images/do.php?id=1234

And i have to change them all to this format

https://example.com/images/1234.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. So i need something like that

UPDATE post SET message = REPLACE(message, 'https://example.com/images/do.php?id=', 'https://example.com/images/{id}.png');

I'm absolutely no experienced SQL user, so can you please help me out?

Edit//

Now i have entries like that:

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

What is the query that i need to add the static file extension? So my entries looks like that:

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

The length of the ID's are between 4 to 6 characters. The main problem here why i can't just add the extension is, because my message row does contain more text than just the URL to modify. Such a row can look like that:

Here is your image link: [LINK]https://example.com/images/1234.png[/LINK] You can view it now.

Edit2//

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]

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.

Answer 1

For your second problem:

UPDATE post
SET message = concat(message,'.png')

Just extend your current entry with '.png' :-)

EDIT: So, my bad! You should give Regex a try:

UPDATE post
SET message = CONCAT(REGEXP_SUBSTR(path,'.*https:\/\/example\.com\/images\/[0-9]{4,6}'),'.png',REGEXP_SUBSTR(path,'\[\/LINK\].*'))

This statement asserts that there is no file extension existing. So you can run the statement without where clause because the file extension won't get doubled. For being sure you can check it before with:

SELECT
    message
    ,CONCAT(REGEXP_SUBSTR(path,'.*https:\/\/example\.com\/images\/[0-9]{4,6}'),'.png',REGEXP_SUBSTR(path,'\[\/LINK\].*')) as corr_message
FROM path

Check the pattern as well on e.g. Regex101.com with your given example as string

Here is your image link: [LINK]https://example.com/images/1234[/LINK] You can view it now.

and the following as pattern

(.*https:\/\/example\.com\/images\/[0-9]{4,6})(\[\/LINK\].*)

The parenthesis builds groups. The first group is for the first part of the string. In update, this is the first Part of Concat. Afterwards we will set '.png'. The third part withing your update statement is represent with the second group of the regex-pattern.

Hope, this will help you. :)

EDIT2: Alright, this will fit to your rows.

UPDATE post
SET message = CONCAT(REGEXP_SUBSTR(path,'.*https:\/\/example\.com\/images\/[0-9]{4,6}'),'.png',REGEXP_SUBSTR(path,'\[\/(IMG|LINK)\].*')) 
WHERE message LIKE '%https://example.com/images/%'

Pattern for checking it:

(.*https:\/\/example\.com\/images\/[0-9]{4,6})(\[\/(IMG|LINK)\].*)

As select for checking your data before update:

SELECT
    message
    ,CONCAT(REGEXP_SUBSTR(path,'.*https:\/\/example\.com\/images\/[0-9]{4,6}'),'.png',REGEXP_SUBSTR(path,'\[\/(IMG|LINK)\].*')) as corr_message
FROM path
WHERE message LIKE '%https://example.com/images/%'

Hope this fits now for your case :)

Answer 2

This answers the original version of the question.

This works for the example in your question:

select concat(substring_index(path, 'do.php', 1),
              substring_index(path, '=', -1),
              '.png')
from (select 'https://example.com/images/do.php?id=1234' as path) x

You can easily turn this to an update:

update post
    set message = concat(substring_index(message, 'do.php', 1),
                         substring_index(message, '=', -1),
                         '.png')
    where message like '%do.php%=%';