CODEWITHSUNDEEP
mysqlsql
Totte Karlsson
Totte Karlsson

Reputation: 1341

How to find the longest matching string from a input string

A table, named strings, contains various strings, and unique id's:

enter image description here

Question is, how to write a query that takes an input string, and return the id of the longest matching (sub) string in the strings table?

The matching string, may, or may not be a substring of the input string. Comparison starts at string index 0.

Inputs and expected output:

INPUT -> OUTPUT
ABC    -> 1
ABCD   -> 2
ABCKK  -> 1
ABCDDD -> 2
DAB    -> NULL
CDE    -> NULL

Doing this:

SET @theString = 'ABCBBB';
SELECT id FROM strings WHERE a_string LIKE @theString;

only returns the correct result when the input string exactly matches a string in the table, and don't work when the 'a_string' is a substring.

Upvotes: 0

Views: 375

Answers (2)

Jetto Martínez
Jetto Martínez

Reputation: 999

In regards of using LIKE, you need to set the wildcards for it to work as the filter you want. If you are not required to set a variable, you can use the following query.

SELECT id FROM strings
WHERE a_string LIKE '%ABC%'
ORDER BY length(a_string) DESC LIMIT 1;

or if you need a variable, it can be done with the CONCAT function

SELECT id FROM strings
WHERE a_string LIKE CONCAT('%',@theString,'%')
ORDER BY length(a_string) DESC LIMIT 1;

This just is an alternative to @Gordon Linoff's answer.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use:

select s.*
from strings s
where @theString regexp a_string
order by length(a_string) desc
limit 1;

Upvotes: 1

Related Questions