[FIXED] Removing everything after a string in mysql

Issue

I have a bunch of image file names that i uploaded to wordpress and want to change the file names using sql in phpmyadmin. The files are as such: text-text-text-328x328.jpg text-text-590x236.jpg text-text-text-text-150x150.jpg text-569x348.jpg

tex-text-text.jpg

So i want to remove the file in the title. The only footprint i can thing of is removing string after - plus a number

I know how to remove everything after - :

UPDATE MyTable
SET MyText = LEFT(MyText, CHARINDEX('-', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

Just not sure how to remove everything after - and a number.

Thanks!

Solution

SELECT 
  name, 
  concat(
    substr(name, 1, length(name) - length(substring_index(name,'-',-1)) - 1),
    '.',
    substring_index(name, '.', -1)
  ) as newname
FROM 0_a

Answered By - SIDU