Remove duplicate words from field in mysql

Question

I am wondering if it is possible to remove duplicate text using a mysql query from one field, or if a problem like this would be better solved using PHP.

I have a database where users enter tags which can be searched upon. I have noticed that some tags have synonyms which I want to add to the field, but in some cases the synonym already exists, in other cases not. For example after I have updated the tags, I can end up with the following (the tags are separated by spaces only):-

1. pool swimming pool ocean sea water
2. swim ocean sea water water swim
3. swimming pool swim swimming pool swim

Is there a way to eliminate duplicate text from the same field so I end up with this?

1. pool swimming ocean sea water swim
2. ocean sea water swim
3. pool swim swimming

Answer 1

The model you describe (all tags into a single cell, separated by spaces) is not normalized so you can't expect to find a simple, performant and reliable way to do stuff with it from the database server (beyond reading the column). The way it's now, PHP is your only chance to do the cleanup you are planning to do, and you'll have to retrieve every row.

Is it too late to make a little change in the database design? If you store each tag into a separate row in a tag table you'd be able to do lots of stuff from plain SQL.