How to JOIN table ON .. LIKE ... 'table.variable'

Question

I have two tables and I am trying to JOIN them and use the LIKE function on mySQL.

My initial code was :

select A.column
from A
join B
  on (B.column) like ('%A.column%');

then I searched stackoverflow past answers and I found that my version was not wrong and at the same time I found this version which did not work either:

select A.column 
from A
join B 
  on B.column like '%'+ A.column +'%';

In the end I used this one :

select A.CPM , B.column , A.column 
from A
join B 
  on B.column like concat('%', A.column,'%');

and it worked. My question is why didn't the previous versions work? Is it because of mySQL version? From my research the syntax is correct in all 3 versions. The results on mySQL for the first two were blank though.

Answer 1

First: Won't work

select A.column
from A
join B
  on (B.column) like ('%A.column%');

Reason:

This is just string literal, hardcoded value

B.column LIKE '%A.column%'  

Second: Won't work

select A.column 
from A
join B 
  on B.column like '%'+ A.column +'%';

Reason:

+ is not for string concatenation but for addition. Example:

SELECT '%' + 'a' + '%' -- result 0, mysql tries to convert to numbers and compute sum.

Third: Will work

select A.CPM , B.column , A.column 
from A
join B 
  on B.column like concat('%', A.column,'%'); 

Reason:

You build last value at runtime using correct function CONCAT