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!

would seem he has a set of columns with something similar in the column name, and wants to see if his 'value' is contained in any of these... — Irfy, Aug 27 '09 at 16:05
As I read it, the column data is `"test"` and his search term is `"this is a test"` — nickf, Aug 27 '09 at 16:12
One scenario is that rows represent key words, and he wants to find which keywords (column Values) are in the candidate text (@Value) — Charles Bretana, Aug 27 '09 at 16:16
Possible duplicate of [MySQL: What is a reverse version of LIKE?](http://stackoverflow.com/questions/472063/mysql-what-is-a-reverse-version-of-like) — joshweir, Mar 24 '16 at 03:51

Eric · Accepted Answer · 2009-08-27T16:33:10.980

12

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.

edited Aug 27 '09 at 16:33

answered Aug 27 '09 at 16:04

Eric

92,005
12
114
115

1

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 Aug 27 '09 at 16:15

score 3 · Answer 2 · answered Aug 27 '09 at 16:05

3

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

answered Aug 27 '09 at 16:05

LukeH

263,068
57
365
409

MySQL question about "reverse LIKEs"

2 Answers2