working on a code where we are storing images but some images ending with weird characters

like , , -x1 to x10 etc or more but always end wih a .jpg

how can i regex to replace the image name to be a valid name

here is an example of what i have

PCpaste_10_g,-X1,-X2,-X3
SNBar_NEW,-X1

they can go till -X10

so i want to have regex to remove ,and everything afterwards it

i tried using replace but that only works for one item at a time

CodePudding user response：

If your data is consistent with the string before the first comma that need to be taken, then you can try with SUBSTRING_INDEX;

Let's use this as you sample table & using your sample data:

CREATE TABLE mytable (
val VARCHAR(255));

INSERT INTO mytable VALUES
('PCpaste_10_g,-X1,-X2,-X3.jpg'),
('SNBar_NEW,-X1.jpg');

Then first you extract the first string before comma occurrence:

SELECT SUBSTRING_INDEX(val,',',1) extracted
FROM mytable

returns

Then to add back .jpg:

SELECT CONCAT(SUBSTRING_INDEX(val,',',1),'.jpg') extracted_combined
FROM mytable

IF your image extension is not consistently .jpg, you can do another SUBSTRING_INDEX() to get the extension then CONCAT() them:

SELECT CONCAT(SUBSTRING_INDEX(val,',',1) ,'.',
        SUBSTRING_INDEX(val,'.',-1)) Extracted_combined
FROM mytable;

Demo fiddle

CodePudding user response：

You can use LOCATE to find the first occurrence of "," in the field and LEFT to grab everything up to the first "," -

SET @value := 'PCpaste_10_g,-X1,-X2,-X3';
SELECT CONCAT(LEFT(@value, LOCATE(',', @value) - 1), '.jpg');

or for your update -

UPDATE <table>
SET image_name = CONCAT(LEFT(image_name, LOCATE(',', image_name) - 1), '.jpg')
WHERE image_name LIKE '%,%';

or to handle your , at the same time -

UPDATE <table>
SET image_name = CASE
    WHEN image_name LIKE '%,%'
        THEN CONCAT(LEFT(image_name, LOCATE(',', image_name) - 1), '.jpg')
    WHEN image_name LIKE '%\,%'
        THEN CONCAT(LEFT(image_name, LOCATE(',', image_name) - 1), '.jpg')
    END
WHERE image_name LIKE '%,%'
OR image_name LIKE '%\,%';