MySQL: select the closest match?

Question

I want to show the closest related item for a product. So say I am showing a product and the style number is SG-sfs35s. Is there a way to select whatever product's style number is closest to that?

Thanks.

EDIT: to answer your questions. Well I definitely want to keep the first 2 letters as that is the manufacturer code but as for the part after the first dash, just whatever matches closest. so for example SG-sfs35s would match SG-shs35s much more than SG-sht64s. I hope this makes sense whenever I do LIKE product_style_number it only pulls the exact match.

Answer 1

You will probably want to do it as a stored procedure, as I expect that the algorithm may not be trivial.

For example, you may split the term at the -, so you have two parts. You do a LIKE query on each part and use that to make a decision.

You could just loop though, replacing the last character with "%" until you get at least one result, in your stored procedure.

Answer 2

There normally isn't a simple way to match product codes that are roughly similar.

A more SQL friendly solution is to create a new table that maps each product to all the products it is similar to.

This table would either need to be maintained manually, or a more sophisticated script can be executed periodically to update it.

If your product codes follow a consistent pattern (all the letters are the same for similar products, with only the numbers changing), then you should be able to use a regular expression to match the similar items. There are docs on this here...

Answer 3

Sounds like you need something like Lucene, though i'm not sure if that would be overkill for your situation. But it certainly would be able to do text searches and return the ones most similar first.

If you need something more simple I would try to start by searching with the full product code, then if that doesn't work try to use wildcards/remove some characters until you return a result.

Answer 4

It sounds like what you want is levenshtein distance .

Unfortunately, there isn't a built-in levenshtein function for mysql, but some folks have come up with a user-defined function that does it.