Use LIKE %..% with field values in MySQL

Question

I stumbled into a delicate SQL problem when I needed to use a value from a field inside a LIKE %..% statement.

Example:

SELECT t1.Notes, t2.Name
FROM Table1 t1, Table2 t2
WHERE t1.Notes LIKE '%t2.Name%'

This is only an example from the top of my head to show what I need to do (I know this will not work). I need to use the value of t2.Name inside the LIKE %..%

I guess this is trivial when you know it ;)

score 170 · Accepted Answer · answered Dec 12 '10 at 05:44

170

Use:

SELECT t1.Notes, 
       t2.Name
  FROM Table1 t1
  JOIN Table2 t2 ON t1.Notes LIKE CONCAT('%', t2.Name ,'%')

answered Dec 12 '10 at 05:44

OMG Ponies

314,254
77
507
490

1

Thanks, that did the trick! Aa I said, it is trivial when you know it. However, I stumbled into the next problem... But I will make a new question. Thank you! – Max Kielland Dec 12 '10 at 12:59
Any solution for all databases? – Loc Dec 07 '13 at 02:54
Any Solution to use LIKE %..% with field in IF statement? – TechCare99 Mar 04 '15 at 06:52
6

Technically you might want to escape the field in case it has % in it: LIKE CONCAT('%',REPLACE(field,'%','\%'),'%') – dlo May 21 '15 at 21:58

score -7 · Answer 2 · edited Jul 27 '16 at 11:13

-7

  SELECT t1.a, t2.b
  FROM t1
  JOIN t2 ON t1.a LIKE '%'+t2.b +'%'

because the last answer not work

edited Jul 27 '16 at 11:13

Behzad

3,314
4
36
61

answered Jul 27 '16 at 10:23

NT4.info

1

Use LIKE %..% with field values in MySQL

2 Answers2

Linked