CODEWITHSUNDEEP
mysqlstring-comparison
Arnie
Arnie

Reputation: 681

Figuring out if 2 strings are similar in MySQL?

I have a table catalog_product_entity_varchar and a column value. I tried comparing strings with SOUNDS LIKE. That works well most of the time, but not for all cases.

Where it works: SELECT value FROM catalog_product_entity_varchar WHERE value SOUNDS LIKE 'Stechbeitel m.Schlagkappe 6mm';

This gives me other results such as Stechbeitel m.Schlagkappe 20mm, which is ok.

Another query however does not work: SELECT value FROM catalog_product_entity_varchar WHERE value SOUNDS LIKE '300 mm Hygiene Staender St.weissalu.';

Its supposed to find a string 300 mm Hygiene Staender St.schwarz, but it doesnt. Whats the reason here? I tried the soundex() values for 300 mm Hygiene Staender St.schwarz and 300 mm Hygiene Staender St.weissalu. and they are of course not the same, but almost the same, which makes sense. The soundex() values are M25235362324 and M252353623262.

So, how could I either make the SOUNDS LIKE work for all similar strings or maybe match the soundex() values, as they are very similar?

Thanks!

Upvotes: 1

Views: 562

Answers (1)

lemon
lemon

Reputation: 15502

You could try to apply the SOUNDEX operator twice. The idea here is that similar strings will have slightly different soundex coding, but whose codes will have the same soundex sound.

SELECT value_ 
FROM catalog_product_entity_varchar 
WHERE SOUNDEX(SOUNDEX(value_)) = SOUNDEX(SOUNDEX(<your_comparison_string>))

Check the demo here.

Upvotes: 1

Related Questions