how update value between 2 strings, please ?

hello, i want to update data between two strings.

phrase = "string1 data here string2"
phrase = "string1 different data string2"
phrase = "string1 whole different data string2"

so i have multi rows, i wanna use one query to update all the data between "string1" and "string2"..
and after the update, the phrases will be like this:-

phrase = "string1 my data string2"
phrase = "string1 my data string2"
phrase = "string1 my data string2"

so any idea about the query please ?

 

You would need to use a regex however, mysql doesn't support using a regex in an update situation unless you recompile it with an additional extension. http://www.mysqludf.org/lib_mysqludf_preg/

I would write a php or other script to loop through and update the rows.

 

Hi,

There is a solution, because this is a particular case (as described) :

DROP TABLE IF EXISTS TESTING;

CREATE TABLE TESTING(phrase VARCHAR(255));

INSERT INTO TESTING (phrase) VALUES('string1 data here string2'),
('string1 different data string2'),
('string1 whole different data string2'),
('string3 data here string4');

SELECT * FROM TESTING;

Code (markup):

UPDATE TESTING SET phrase='string1 my data string2' WHERE 
TRIM(phrase) REGEXP '^string1[[:>:]].*[[:<:]]string2$';

SELECT * FROM TESTING;

Code (markup):

Regards

 

UPDATE tablename SET phrase=CONCAT('string1', ' mydata ', 'string2') WHERE phrase LIKE 'string1%string2';

 

this only works for phrase = "string1 data here string2" ...
but it doesn't work for phrase = "something_here string1 data here string2 another_thing" !!!

 

It doesn't work for a lot of things (it won't increase your gas mileage, it won't clear up your acne, it will have little to no affect on the size and girth of your member), but for the initial question and examples you posted it does.

 

LIKE 'string1%string2';

is not going to match

"something_here string1 data here string2 another_thing" !!!

You could use LIKE '%string1%string2%';

But then you replace something_here string1 and string2 another_thing with string1 and string2 and don't retain the something_here and another_thing values.

You need to loop through using a regex. The mysql extension or using a script to do it is the only way.