Find best matching row in MySQL (InnoDB)

mysql, regex

Solution

Just use LIKE, but the other way around to what your probably used to.

select query
from table1
where 'engine/mail/key' like concat(query,'%')
order by length(query) desc
limit 1

Results:

mysql> select * from query;
+-------------+
| query       |
+-------------+
| engine      | 
| engine/pdf  | 
| engine/mail | 
+-------------+
3 rows in set (0.00 sec)

mysql> select query from query 
       where 'engine/mail/key' like concat(query,'%') 
       order by length(query) desc 
       limit 1;
+-------------+
| query       |
+-------------+
| engine/mail | 
+-------------+
1 row in set (0.01 sec)

Problem

I have the following test string `engine/mail/key` and a table which looks like this: ``` +-------------+ | query | +-------------+ | engine | | engine/pdf | | engine/mail | +-------------+ ``` I want to find the best matching row. Best match is specified by the most matching characters from the start of the string/row. I have constructed a RegExp, but it of course matches all rows and won't give me any info about which matched the most chars/parts. Regexp: `^engine(/mail(/key)?)?` I had an other idea about using MySQL's `FIND_IN_SET` function like this: ``` `FIND_IN_SET(query,'engine,engine/mail,engine/mail/key')` ``` And order the result by it's output. That would work, but it's not in any way a nice solution. Does anybody have an better idea about this?

Original source