delete every occurence of a phrase

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
deezin
Forum Newbie
Posts: 9
Joined: Sat May 11, 2002 3:21 pm

delete every occurence of a phrase

Post by deezin »

Hi. I know mysql but I don't know how to delete every occurence of a particular phrase. For example if the phrase "I love you" occurs somewhere is the description of a project how do I delete that phrase without deleting the actual record?

If I type in delte from projects where description like = 'I love you';

then the actual records will be deleted, but I want only the phrase itself, not the record to be deleted. Does anyone know how to do that? Do I have to do it in PHP or it possible to do it in MySQL?

Thanks for your help.
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

how about:

Code: Select all

UPDATE projects SET description='' WHERE description like='I love you';
deezin
Forum Newbie
Posts: 9
Joined: Sat May 11, 2002 3:21 pm

delete every occurence of I loveyou

Post by deezin »

Hi. Thank you for your quick reply. You said the answer was this:

UPDATE projects SET description='' WHERE description like='I love you';

Wouldn't this delete the rest of the description? For example the description might be "Blah blah blah, I love you, blah blah' and then we would lose the blah blahs, wouldn't we?

Thanks again.
Rachel
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

oh, thought you wanted anything with 'I love you' deleted. What you want to do is select all entries with 'i love you' in them, read them in a loop, run a str_replace on them, then update each one.
fatalcure
Forum Contributor
Posts: 141
Joined: Thu Jul 04, 2002 12:57 pm
Contact:

Post by fatalcure »

uhm, just do:

Code: Select all

$query = "SELECT id, description FROM table";
$result = query_db($query);

while ($row = mysql_fetch_array($result)) {
     $id = $rowї"id"];
     $description = str_replace("I love you", "", $rowї"description"]);
     
     $updatequery = "UPDATE table SET description = '$description' WHERE id = $id";
     query_db($updatequery);
}
you could do something like that :)
deezin
Forum Newbie
Posts: 9
Joined: Sat May 11, 2002 3:21 pm

Thank you

Post by deezin »

:lol: :P

Thank you VERY much. This worked wonderfully. I really appreciate your kindess.

Rachel
AllFreelanceWork.com
Post Reply