Search and Replace Query?

[mrsbean]

I want to replace a string inside a text field named "text".

Here is a query which would show the records which contain the offending code which I want to strip out:

SELECT * FROM articlestable where text like Concat('%', '<b class=titler>', '%', '</b>', '%')

I want to remove <b class=titler>ANYTEXT BETWEEN THE TAGS </b>

The table is large, about 13,000 rows. Can anyone direct me how to write the query which would strip out this title line from inside the text or direct me to a PHP script someplace that would do the trick?

Thanks in advance.

MrsBean

 

[TonyGroves]

You could use:
[tt]
UPDATE articlestable
SET `text`=
CONCAT(
SUBSTRING_INDEX(`text`,'<b class=Titler>',1),
SUBSTRING_INDEX(`text`,'</b>',-1)
)
WHERE `text` LIKE '%<b class=Titler>%</b>%'
[/tt]
This query assumes that there is only one occurrence of each of those two tags in the text. If there is more than one '</b>' for example, then you would need to use PHP to do a regular-expression replace on each record individually.

You might also like to change the field name to something other than 'text', which is a reserved word in MySQL.

 

[guelphdad]

Actually Tony you are only half right about TEXT being a reserved word. For some strange reason there are about a half dozen words that are reserved words that can be used anyway. Or as the mysql site says:

MySQL allows some keywords to be used as unquoted identifiers because many people previously used them.

Sad really isn't it?

 

[lgarner]

Sometimes a word becomes reserved in a newer version. Better not to have to rewrite all the existing code to accomodate it.

I'd really prefer an option similar to what's available for handling old passwords between 4.0 and 4.1.

 

[TonyGroves]

I've noticed that some keywords are accepted as user-defined names, though I wonder if this acceptance is context-dependent. However, it's always better to play on the safe side.

Of course, that's one of the major drawbacks of SQL - the ever-increasing plethora of keywords. And MySQL's published list is far from complete, so you don't always know for certain if a user-defined name is safe to use. I find it surprising that SQL has not only managed to avoid following its equally-awkward cousin COBOL into the graveyard, but has beaten off all competition (if there ever was any) to become the only serious database language around. But then, being a C++/Perl user, I would say that.