Remove duplicate text from field

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

You may consider keeping one entry per tag instead of all tags as a string, so that you could do a select distinct among other things.

Answer 2

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.

Answer 3

If it's a real option,

Change your database design. I don't know about your time constraints so it may really not be an option, but consider which of these two paths you'd rather go down:

• A couple of hours now redesigning the database, then writing, debugging and verifying a script that'll take all the values from the existing layout and put them in the new one.
• Hours and hours later coming up with obscure queries for otherwise simple tasks that would take ten minutes to write a query for if the database was designed the way a relational database should be.

If it's really not an option though...

Let Sentence = the string of words.
Split Sentence up on every space and build an array out of it*. Store this as Words.
Let UniqueWords = an array of words with no duplicates.
For each Word in Words:
     If the Word is not in UniqueWords, put it in.

*a la PHP explode

You could also process it as a raw string (stopping to check at spaces or EOL), which may be faster, but if speed is important, your current database design should be far more concerning than this loop.

EDIT: I didn't see that you wanted it in a SQL query. I'm not sure it'd be possible using a query; perhaps a stored procedure will do. I don't know how to use those though.

Answer 4

+1 redesign, but if redesign is not an option now...

How many distinct tags are there? You might be able to do this using CASE and substring functions.

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

Answer 5

IMO, you're best to handle this with PHP

$uniqueTags = array_unique(explode(' ', $tagsFromDbColumn));