I'm asking a question about the concatenation function and regex.
I have a fied (reference) which looks like :
FW123
TS76
FWE8E
K987
HT78
FW345
I would like to concatenate all rows which begins by FW with EU at the end.
So the result must looks like :
FW123EU
TS76
FWE8EEU
K987
HT78
FW345EU
This is my command line :
UPDATE ps2_product
SET `reference` = CONCAT(^FW[A-Z0-9],EU)
It's the first time I handle regex expressions. The syntax is correct ?
Thank you,
You need to add a where condition.
UPDATE ps2_product
SET `reference` = CONCAT(reference,'EU')
WHERE reference REGEXP '^FW[A-Z0-9]+$'
^FW[A-Z0-9]+$ to only match those entries that start with FW and then have 1 or more uppercase ASCII letters or digits up to the end of string.
you can use LIKE :
UPDATE ps2_product t
SET t.`reference` = CONCAT(t.`reference`,'EU')
WHERE t.reference LIKE 'FW%'
FW%, even such entries as FW-### will get modified, while it seems OP needs to make sure there are uppercase ASCII letters or digits after FW.You can do this by :
1) Using LIKE and CONCAT :
UPDATE ps2_product
SET reference = CONCAT(reference,'EU')
WHERE reference LIKE 'FW%';
2) Using CONCAT, REGEX with WHERE :
UPDATE ps2_product
SET references = CONCAT(references,'EU')
WHERE references REGEXP '^FW[A-Z0-9]';
3) Using REGEX, CONCAT and case :
UPDATE ps2_product
SET references =
CASE
WHEN references REGEXP '^FW[A-Z0-9]' = 1 THEN CONCAT(references,'EU')
WHEN references REGEXP '^FW[A-Z0-9]' = 0 THEN references
END;
