hamboy75 hamboy75 - 4 months ago 8
MySQL Question

Mysql find row with longest field value as beginning of a text

I have something like this in a mysql table.

ID Text
1 a
2 b
3 a-b
4 b-a
5 a-b-c
6 a-b-d


Usually you search a text inside a column, i just want the opposite, i want a query to search all columns inside a text. I dont know if it is possible.


if text is "a-b-f" It must return query with ID 3 ("a-b")

if text is "a-c" It must return query with ID 1 ("a")

if text is "b-a" It must return query with ID 2 ("b")

if text is "b-b" It must return query with ID 2 ("b")

if text is "a-b-c-d-e-f" It must return query with ID 5 ("a-b-c")

if text is "c-a-a" It must return an empty query


Thanks for the help.

PD: I was looking for something like this but all ways I found was search a text inside a column value and it is the opposite way as I said.

Answer

Could be using like

 select ID from my_table  
 where  'your_text_seach'   like   concat ( text, '%')
 order by length(text) desc
 limit 1
Comments