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

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 2

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(deadlink).