MySQL: Remove numeric string between WordPress uploads path

Question

Long story short.. WordPress S3 offload plugin left some images with something like /uploads/2020/10/182739640/filename-e324923424.jpg

The random number string (182739640) is not fixed. It's got several numeric combinations.

I'm looking for a solution to target and remove the random number strings " 82734642 " after /uploads/2020/10/ and before /filename.jpg .

So the results will be; /uploads/2020/10/filename-e324923424.jpg

When I looked up the forums, I did find this; https://stackoverflow.com/a/36492830/7142359, though this may remove all numerics in the full path later - which I do not want.

Some assistance with this will be helpful.

Thanks in advance.

Answer 1

I understand that you need to remove all the numeric values between second and third last slashes. Since this appears like a one-off exercise, I would recommend doing this directly on the database (once tested on local db)

You would need to use REPLACE method in the query and target the unwanted numeric values by obtaining them using SUBSTRING_INDEX.

The UPDATE Query: UPDATE images SET title = REPLACE(title, concat(substring_index(substring_index(title, "/", -2), "/", 1), "/"), "")

Assuming the table name is images. Alter as required Assuming the filed name is title. Alter as required "SET" allows you to update the field "REPLACE" allows you to replace the string (REF. https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php) "concat" allows you to join the target numeric string with a slash in the end "SUBSTRING_INDEX" allows you to split the value using a delimiter and in this case the "/". Worth adding a where clause to run the update query only on few records to begin with.

NOTE: Please run a select query prior to running an update to see if you have grabbed the correct string.

EG. SELECT title as title_before, substring_index(substring_index(title, "/", -2), "/", 1) as string_to_be_replaced, REPLACE(title, concat(substring_index(substring_index(title, "/", -2), "/", 1), "/"), "") as title_after FROM images IF you notice the title_after is the correct one, then proceed to run the update query a) on your local db using WHERE clause to limit the updates to few records. b) running it then on the production after taking a backup of the table.

Hope this helps.