I'm trying to find similar values for a single column. I have a column 'name' and I've used a query to find duplicates, however I'm fairly certain that there are similar...entries? Not duplicates per say, but rather typos?

I realise I myself am not exactly sure what I'm trying to do, an example would be: table.name - apple and I want to be able to sort all table.names that are similar to it, like 'applle'.

To note: I dont want to compare table.name with a certain word but rather sort all instances of table.name that are similar

enter image description here

Is it possible to search through 'name' and compare it with all other 'name' values?

Second thing, which is, how to phrase it - complementary values. (e.g. "3d audio" vs "3d audio, entertainment and music" So, if 'name' has '3d audio' in one instance and in another '3d audio, blah blah' to have them sorted in some way ?

  • Try soundex() for typos. – mustaccio Feb 11 at 13:55
  • I'm a bit baffled as to how to use it, don't really have much knowledge, I tried to select SELECT SOUNDEX(pt.name) FROM product_taxonomy pt Can I actually even loop it to compare name with name? Anyway, thank you. Will read more about how to use it tomorrow and see if I can utilise it. – Ruthock Feb 11 at 14:05
  • What volume of data and target latency would you have for this project? MySQL doesn't have great tools for approximate string search, but as long as your requirements are not high, or with the assistance of external tooling, there are many ways to make it work. – jynus Feb 11 at 15:19
  • Hey, so it is a table that has , em roughly 2000 entries for the specific column? Sorry not familiar with proper terms. Could you suggest some tools that I can look up? Thank you. – Ruthock Feb 12 at 7:08
  • There are a few similar questions. Most have multiple answers, all of which are inadequate. Search stackoverflow.com for "[mysql] soundex". (Soundex is generally useless because it handles only the first couple of syllables.) – Rick James Feb 16 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.