MySQL question about "reverse LIKEs"

Question

Well given I have a value I want to check for potential matches in a database (in one varchar field) so I write something like:

SELECT * FROM table WHERE column LIKE "%value%"

Which will work if the value is something like "test" and the column has a value of "this is a test" however if it is reversed then I will not get a match I have tried things along the lines of:

SELECT * FROM table WHERE CONCAT("%",column,"%") LIKE "value"

but don't know exactly how to phrase this to Google to get a response I need, please help!

Answer 1

You can reverse a like statement. Just using the same syntax as a regular like query:

select
    *
from
    table
where
    'value' like concat('%', column, '%')

Of course, if you felt wild and crazy, you could also use instr:

select * from table where instr('value', column) > 0

I don't know which one is faster, since I don't have a MySQL instance to test against, but it's worth trying both to see which wins.

Answer 2

SELECT *
FROM table
WHERE 'value' LIKE CONCAT('%', column, '%')