How to remove "similar" but not identical content in a MySQL database.

Question

Suppose I have this table:

ID | description
-------------------
5  | The bird flew over the tree.
2  | The birds, flew over the tree

These two rows have "similar" content. How would I remove #2?

1. What algorithm should I use for "similar" text?
2. How would I do this with Python?

Thanks!

Answer 1

Typically, for each value, you'd create a 'simplified' value (remove whatever wasn't essential ... in your example, the punctuation and pluralization), and then compare the simplified values for equality.

Answer 2

Look here for some inspiration.

Answer 3

You could use the LIKE operator.

DELETE FROM myTable WHERE description LIKE 'The bird%flew over the tree%';

Answer 4

What you could try is stripping necessary punctuation and running each sentence through a stemmer (e.g. a Porter Stemmer).

Once you have a stemmed version of the sentence you could store that in another column for comparison. However, you may find it more space efficient to hash the stemmed sentence if the sentences are long (e.g. over 40 chars on average).

Any rows which share the same stemmed sentence or hash will be highly likely to be equivalent - you could automate their removal, or create a UI to enable a human to rapidly approve each one.

Here's a Python implementation of the Porter stemmer.