Search code examples
phpmysqlsqlstringstring-matching

How to find longest match in a query sentence using SQL?

Let say I have a table like this:

----------------------------------
|id |phrase
----------------------------------
|1  |real
|2  |testing
|3  |real message
|4  |testing message
|5  |special testing message

The target is to find id of the longest match in a query sentence. For example, a query sentense This is a testing message should return 4.

I am using PHP with MySQL, so if it is too complicated to do in SQL alone, part of the operation can be switched to PHP. Currently I am getting the whole table out and do regex one by one on PHP, but it is inefficient if the table get large. Is there a way to switch some workload to SQL so that I don't need to store the whole table into memory?

Solution

  • Use CONCAT, LENGTH with LIMIT:

    SELECT id FROM table
WHERE ('.$search_term.' LIKE CONCAT("%",phrase,"%"))
ORDER BY LENGTH(phrase) DESC LIMIT 1