SQL search for and replace feature

[sunseeker]

im looking for a way in my smf db's to do a SQL search for and replace.

anbody know an easy way to do this?

either by using an sql query?

or even one at a time modifications using the cpanel MySQL Databases or phpMyAdmin directly on each reacord?

if you do have an exact SQL query to paste here that would be much appreacited!

thanks!

[sm2k]

Via phpMyAdmin you can run queries on your database pretty easily.  A query to replace all occurances of some string in a column with a new string would look like this:

UPDATE sometable SET somecolumn=REPLACE(somecolumn, 'oldstring', 'newstring')

You might make a new table to test your query in so that you don't hose anything important

[sunseeker]

thanks!

is that the exact syntax all the way around?

if not, then anybody that has that handy please post if you can. i can replace your db name with mine adn such so dont worry. id rather see the exact query.

i wish i could just edit the data in place.

much appreciated!

[kegobeer]

What table do you want this query for?  What do you want to replace?

[sm2k]

thanks!

is that the exact syntax all the way around?

if not, then anybody that has that handy please post if you can. i can replace your db name with mine adn such so dont worry. id rather see the exact query.

i wish i could just edit the data in place.

much appreciated!

You'd use the query I specified to replace a string value in some table column with a new string value.  So if you wanted to replace all occurances of the word 'taco' with 'burrito' in any message on your forum, the query would be:


UPDATE SMF_messages SET body=REPLACE(body, 'taco', 'burrito')

If what you're trying to do is something simpler, like changing all occurances of poster name 'Bob' to 'Jimbo' in your SMF messages table, you'd just do something like:

UPDATE SMF_messages SET posterName='Jimbo' WHERE posterName='Bob'

You'll have to tell us exactly what you're trying to do.

[luiscamino]

hi, i have a similar problem of newbieness with sql queries.

i just need to search for some text in a post. i accidentally deleted the FAQ in my forum but should be in the backup tables, in which i would like to search for the word "FAQ" in order to find it.

thanks in advance,