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 ;)

OMG Ponies · Accepted Answer · 2010-12-12 05:44:15Z

183

Use:

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

answered Dec 12, 2010 at 5:44

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Max Kielland Over a year ago

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!

LHA Over a year ago

Any solution for all databases?

TechCare99 Over a year ago

Any Solution to use LIKE %..% with field in IF statement?

dlo Over a year ago

Technically you might want to escape the field in case it has % in it: LIKE CONCAT('%',REPLACE(field,'%','\%'),'%')

Behzad · Accepted Answer · 2016-07-27 11:13:08Z

-8

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

because the last answer not work

edited Jul 27, 2016 at 11:13

Behzad

3,5904 gold badges38 silver badges64 bronze badges

answered Jul 27, 2016 at 10:23

NT4.info

1

Collectives™ on Stack Overflow

Use LIKE %..% with field values in MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related