ereg sql updates (or something similar)

stevesweetland

ok, heres the craic;

i have a database table

ID (mediumint auto_inc)
Text (text)

full of 1574 (at present) records. It is currently in testing, but will soon go live. When this happens, practically all of these records have a bit in the 'Text' field that will need replacing with new text.

Id like to be able to write an sql string that will find all the matches, and replace the matches for me, rather than having to churn through each text

For example, if the word i needed to replace was 'Foo' and change it to 'Bar'... id like my sql to be something along the lines of

SELECT * FROM Table WHERE Text = '%Foo%' OH AND WHILE YOURE AT IT REPLACE ALL INSTANCES OF '%FOO%' TO 'BAR';

i CAN write a php file to parse all these fields, get the text, do a ereg_replace / str_replace on the field and update the record using the filtered text, but id like to find a better way, if there is one.

lazzerous

as far as I know you can't update a record and only modify the content within a field unless it's purely numeric, so you might have to deal with writing a script to select all fields where 'Foo' exists, alter the data, and run update queries.

sidney

yes you can this query will work

mysql_query("UPDATE mytable SET state=REPLACE(state, 'New', 'Old') WHERE LOCATE('New',state)>0");

lazzerous

oooh - i didnt know about that :-D

stevesweetland

Originally posted by lazzerous
as far as I know you can't update a record and only modify the content within a field unless it's purely numeric, so you might have to deal with writing a script to select all fields where 'Foo' exists, alter the data, and run update queries.

will this work even i its only part of a fields data? so itd have this effect

1 'this foo is foo a foo string foo'
2 'so foo is foo this foo'

would become

1 'this bar is bar a bar string bar'
2 'so bar is bar this bar'

cheers for the help!