tags:

views:

91

answers:

2

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!

+9  A: 

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.

Eric
+1 for first answer. This looks good.
Matthew Jones
very cool, had no idea this was even possible!
Irfy
It'll use an index, if one exists for the column, to convert the data - not to find. It's faster to use an inline view in order to add what you want to the column, and THEN compare. If you only want rows where the word occurs, you'd be better off using INSTR.
OMG Ponies
+3  A: 
SELECT *
FROM table
WHERE 'value' LIKE CONCAT('%', column, '%')
LukeH