Edit a TEXT field

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
User avatar
dstefani
Forum Contributor
Posts: 140
Joined: Sat Jan 11, 2003 9:34 am
Location: Meridian Idaho, USA

Edit a TEXT field

Post by dstefani »

Hello all,
I need to edit a comma delimited string in a MySQL TEXT type column.
I want to remove one element of the string.
IE:
column keywords: one, two, three, four, five, six

I want to remove 'four' from the string. // gives: one, two, three, five, six

I thought of doing a 'SELECT keywords FROM products WHERE keyword LIKE %four%'

and then while looping through the resulting recordset use preg_replace()
on each record and then UPDATE that record with the cleaned up string.

Do you know of a more MySQL way of doing this? Some built in function I'm missing?

Thanks,

- D
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Overall, I'd do it as you have suggested with some small changes.

"... RLIKE '[[:<:]]" . $value . "[[:>:]]' ..."

.. might be a better option: a LIKE search will return "fourteen" as well as "four".

str_replace is slightly faster than preg_replace
User avatar
dstefani
Forum Contributor
Posts: 140
Joined: Sat Jan 11, 2003 9:34 am
Location: Meridian Idaho, USA

Post by dstefani »

Thanks!
Post Reply