Geek asks and answers

Why is this MySQL double metaphone feature not working correctly?

I'm just learning metaphone and double metaphone search algorithms, and I have a few questions. On the Metaphone Wiki page, I found several sources with implementations, in particular, the MySQL implementation. I wanted to test it using a test database, so I first imported the metaphone.sql file (containing the double metaphone function) found here

Now I have a table, a country in which there is a list of all countries in the column "name", for example. "Afghanistan", "Albania", "Algeria", etc. So, firstly, I wanted to create a new column in the table to store the double metaphone row of each country. I ran the following code:

UPDATE country SET NameDM = dm(name)

Everything worked correctly. Afghanistan is AFKNSTN, Albania is ALPN, Algeria is ALKR, ALJR, etc. “Amazing,” I thought.

However, when I tried to query the table, I did not get any results. For the author of metaphone.sql, I followed 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 any search query I was looking for, but I got 0 results after every SQL query.

Can anyone explain this problem? Am I missing something important, or am I just misunderstanding the Metaphone algorithm?

Thank!

+3
source share
3 answers

/// . Collation , , , . , , .

select name, length(name), char_length(name), @search, length(@search), char_length(@search) from tbl

.

show variables like 'character%'

.

show create table tbl
+2

dm() , . dm('smith') != dm('schmitt') , .

0.0 1.0 (), , 0.3 - , 0,5 - .

. dmcompare(dm("boothroyd"), dm("boofreed")) = 0.3
dmcompare(dm("smith"), dm("scmitt")) = 0.5

, double metaphone not , , , .

    CREATE FUNCTION `dmcompare`(leftValue VARCHAR(55), rightValue VARCHAR(55)) 
        RETURNS DECIMAL(2,1) 
    NO SQL
    BEGIN
    ---------------------------------------------------------------------------------------
    -- Compare two (double) metaphone strings for potential similarlity, i.e.
    --    dm("smith") != dm("schmitt")  :: "SM0;XMT" != "XMT;SMT" 
    --  dmcompare( dm('smith'), dm('schmitt' ) returns 0,5
    -- @author: P.Boothroyd
    -- @version: 0.9, 08/01/2013
    -- The values here can still be played with
    -- (c) GNU P L - feel free to share and adapt, but please acknowledge the original code
    ---------------------------------------------------------------------------------------
        DECLARE leftPri, leftSec, rightPri, rightSec VARCHAR(55) DEFAULT '';
        DECLARE sepPos INT;
        DECLARE retValue DECIMAL(2,1);
        DECLARE partMatch BOOLEAN;

        -- Extract the metaphone tags
        SET sepPos = LOCATE(";", leftValue);
        IF sepPos = 0 THEN
            SET sepPos = LENGTH(leftValue) + 1;
        END IF;
        SET leftPri = LEFT(leftValue, sepPos - 1);
        SET leftSec = MID(leftValue, sepPos + 1, LENGTH( leftValue ) - sepPos);

        SET sepPos = LOCATE(";", rightValue);
        IF sepPos = 0 THEN
            SET sepPos = LENGTH(rightValue) + 1;
        END IF;
        SET rightPri = LEFT(rightValue, sepPos - 1);
        SET rightSec = MID(rightValue, sepPos + 1, LENGTH( rightValue ) - sepPos);

        -- Calculate likeness factor
        SET retValue = 0;
        SET partMatch = FALSE;
        -- Primaries equal 50% match
        IF leftPri = rightPri THEN
            SET retValue = retValue + 0.5;
            SET partMatch = TRUE;
        ELSE
            IF SOUNDEX(leftPri) = SOUNDEX(rightPri) THEN
                SET retValue = retValue + 0.3;
                SET partMatch = TRUE;
            END IF;
        END IF;
        -- Test alternate primary and secondaries, worth 30% match
        IF leftSec = rightPri THEN
            SET retValue = retValue + 0.3;
            SET partMatch = TRUE;
            IF SOUNDEX(leftSec) = SOUNDEX(rightPri) THEN
                SET retValue = retValue + 0.2;
                SET partMatch = TRUE;
            END IF;
        END IF;
        -- Test alternate primary and secondaries, worth 30% match
        IF leftPri = rightSec THEN
            SET retValue = retValue + 0.3;
            SET partMatch = TRUE;
            IF SOUNDEX(leftPri) = SOUNDEX(rightSec) THEN
                SET retValue = retValue + 0.2;
                SET partMatch = TRUE;
            END IF;
        END IF;
        -- Are secondary values the same or both NULL
        IF leftSec = rightSec THEN
            -- No secondaries ...
            IF leftSec = '' THEN
                -- If there is prior matching then no secondaries is 40%
                IF partMatch = TRUE THEN
                    SET retValue = retValue + 0.4;
                END IF;
            ELSE
                -- If the secondaries match then 50% match
                SET retValue = retValue + 0.5;
            END IF;
        ELSE
            IF SOUNDEX(leftSec) = SOUNDEX(rightSec) THEN
                IF leftSec = '' THEN
                    IF partMatch = TRUE THEN
                        SET retValue = retValue + 0.3;
                    END IF;
                END IF;
            END IF; 
        END IF;
        RETURN (retValue);
    END

, , P.Boothroyd - , ..

,

+3
SELECT * FROM country WHERE NameDM = dm(@search)

, , DM , . , , . :

SELECT dm('Albania')

... ALPN. , ...

SELECT * FROM country WHERE NameDM = 'ALPN'

?

+2
source

More articles:


All Articles