Is it possible to search for a string of text within a mySQL table and replace it with something else? I think this should be possible but Im not sure how to go about executing it.

I've never do it, but I would try something along the lines of:

REPLACE <table> (<Field>) select Replace( <Field>, 'OrgText', 'NewText' ) from <table>;

You may be able to list multiple fields.

Hello,
You may find this (http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#REPLACE) helpful also.

- Smiley

Thanks for the info guys. :) I tried it and heres what happened.

REPLACE bb211_smilies(
smiliepath
)
SELECT REPLACE (
smiliepath,
'{imagefolder}/',
''
)
FROM bb211_smilies

MySQL said:

#1066 - Not unique table/alias: 'bb211_smilies'

Any thoughts? I have no idea what this means.

My guess would be that the names you're using are conflicting

REPLACE bb211_smilies(


FROM bb211_smilies

That doesnt really make any sense though. My understanding of the statement was that "{imagefolder}/" would be removed and replaced with nothing within the bb211_smilies table smiliepath. Could it be that the contents are not limited to simply "{imagefolder}/"? The smiliepath field contains text data like "{imagefolder}/smilie_name.ext" where "smilie_name.ext" is the name of a different smilie in each row. If it would help anyone, I could export a copy of this particular table for testing.

Here is the smilie table.

After a bit more reading, I think replace will substitute a new record for the old, but if I understand correctly you want to leave the record intact and just update a part of it. I was able to do that with a single character here

http://www.webxpertz.net/forums/showthread.php?t=26994

If you use that same method in this case, I think the trick will be to figure out where and how long the target string is. I don't have the solution, yet, but wanted to clarify the goal and suggest replace may not be what you're after.

Originally posted by Phineus
After a bit more reading, I think replace will substitute a new record for the old, but if I understand correctly you want to leave the record intact and just update a part of it. I was able to do that with a single character here

Yup, you go it! :)

http://www.webxpertz.net/forums/showthread.php?t=26994

If you use that same method in this case, I think the trick will be to figure out where and how long the target string is. I don't have the solution, yet, but wanted to clarify the goal and suggest replace may not be what you're after.

I didnt really understand what was in the thread you linked... but what you said here made some sense. The target string to replace will always be the same in every row, because its always "{imagefolder}/" that precedes the filename of the smilie image in the smiliepath field.

> I didnt really understand what was in the thread you linked..

I wanted to link to the last post only (http://www.webxpertz.net/forums/showpost.php?p=146771) but didn't know how....

Anyway. I just ran this in phpmyadmin and it worked a charm.


UPDATE bb211_smilies
SET smiliepath = INSERT(smiliepath,1,13,'MyImageFolder')
WHERE smiliepath LIKE '%{imagefolder}%'


Now we just need to figure out how to do it with random occurrences of the string to be replaced.

PS. Adding drop table to the export would've saved me many a click ;-)

PPS. I have a database of files but I keep the location separate from the file names.

Thanks Phineus. :) If you find out how to look for random occurences that would be really cool, but this might do the trick as is. The part I want to replace will be in the exact same location in every field.

And what would adding the drop table to the export do?

> And what would adding the drop table to the export do?

I would load up the table, then test. Of course, it wouldn't work out on the first shot so I had to drop the table (click view database, click drop table, click to confirm, then click sql page), then load it up again to refresh the data. If drop table is included as part of the dump, then it all gets done when you restore from the file.


Anyway, yes, this should do what you need. For random occurences, I've been using two database handles in the one script. Not pretty but it gets the job done... until we can find better.

Madam Spike (and Phineus!),

UPDATE tablename SET field = REPLACE (field,'search_for_this','replace_with_this'); -- vertical search component

WHERE field -- horizontal search component


Example
UPDATE WebXpertz SET CoolFolk= REPLACE (CoolFolk,'Bad Betty','Madam Spike');

-- optional add-on: (just shows more-of-same syntax)

WHERE CoolFolk LIKE '%Bad Betty%' ;

-- or rather more realistic:

WHERE Department='Forum' AND CityBase='SomePlace';


------
and now for

UPDATE WebXpertz SET *= REPLACE (*,'Bad Betty','Madam Spike'); :)

----
PS I couldn't get spaces on your form, to write clearer code
newbie PeterDoug

I wanted to link to the last post only (http://www.webxpertz.net/forums/showpost.php?p=146771) but didn't know how.You can get a single post by clicking on the post number in a thread, located in the upper-right corner ... on the same line with the date and time ... to the left of the scales icon for the reputation system.