CODEWITHSUNDEEP
mysql
user2079197
user2079197

Reputation: 169

Query to select most matching entry

I have a table:

left  | right
--------------
123   | test
12345 | test2
1234  | test3

Need to find most matching entry in left column and output the entry from right column.

Example: I want to find most matching entry for 12345678 (it is 12345 | test2) and get test2 in output. I have tried locate, but do not know how to output right column.

Upvotes: 0

Views: 96

Answers (4)

Akash
Akash

Reputation: 5012

The answer given by 'Mosty Mostacho' is good, which mentions you need to find the Levenshtein distance. Here's another way to do it, just get all the terms like your input string and then order the result by your LEFT column in descending

SELECT 
  `right` 
FROM 
  mytable 
WHERE 
  '12345678' LIKE CONCAT(`left`,'%') 
ORDER BY 
  `left` DESC 
LIMIT 
  1

SQLFIDDLE

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

You will have to clarify what most matching entry means. I understand you mean calculating a Levenshtein distance for all elements and getting the one with the least of them.

So, first you will have to create a User Defined Function to calculate the distance. Check this link.

Once you have the UDF set up, the query you should run is:

select `right` from t
order by levenshtein('your_word', `left1`)
limit 1

Upvotes: 0

Kuzgun
Kuzgun

Reputation: 4737

DELIMITER $$  
    CREATE PROCEDURE FIND(IN @yourtext varchar(100))

       BEGIN
          DECLARE a INT Default 0 ;
          myloop: LOOP

             IF EXISTS (SELECT * FROM your_table 
             WHERE LeftColumn LIKE '%'+substr(@yourtext,0,length(LeftColumn)-a)+'%')
             SELECT RightColumn FROM your_table 
             WHERE LeftColumn LIKE '%'+substr(@yourtext,0,length(LeftColumn)-a)+'%'
             LEAVE myloop;
             END IF

             SET a=a-1;
             select a;
             IF a=0 THEN
                LEAVE myloop;
             END IF;
       END LOOP myloop;
    END $$

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Try something like this:-

SELECT Coulmn2
FROM Table
WHERE  MATCH ( Coumn1)
AGAINST (  '%$keywords%' )

Upvotes: 0

Related Questions