I am just learning about the Metaphone and Double Metaphone search algorithms, and I have a few questions. Per the Metaphone Wiki page, I found a couple sources with implementations, a MySQL implementation in particular. I wanted to test it out with a test database of mine so I first imported the metaphone.sql file (containing the double metaphone function) found here
Right now, I have a table, country, that has a list of all countries in the 'name' column, e.g. 'Afghanistan', 'Albania', 'Algeria', etc. So, first, I wanted to actually create a new column in the table to store the Double Metaphone string of each country. I ran the following code:
UPDATE country SET NameDM = dm(name)
Everything worked correctly. Afghanistan's metaphone string is 'AFKNSTN', Albania's is 'ALPN', Algeria's is 'ALKR;ALJR', etc. "Awesome," I thought.
However, when I tried to query the table, I got no results. Per the author of metaphone.sql, I adhered to the syntax of the following SQL statement:
SELECT Name FROM tblPeople WHERE dm(Name) = dm(@search)
So, I changed this code to the following:
SELECT * FROM country WHERE dm(name) = dm(@search)
Of course, I changed "@search" to whatever search term I was looking for, but I got 0 results after each and every SQL query.
Could anyone explain this issue? Am I missing something important, or am I just plain misunderstanding the Metaphone algorithm?
Thank you!
take a close look at the collation/character set/encoding (it can be defined down to the column level). Collation defines how strings are compared, but a character set can imply a certain collation be used. Maybe your literal string has a different character set, causing the string comparison to fail.
even this may be revealing
select name, length(name), char_length(name), @search, length(@search), char_length(@search) from tbl
.
show variables like 'character%'
.
show create table tbl