Compare the values ​​of two columns

Question

Is there a way to compare the values ​​of two columns in MySQL? For example, if I have a table:

+----------------+ | Col1 | Col2 | |----------------| | abc | 123abc | | def | 234def | | | 123ghi | +----------------+ 

And I wanted to get all the entries in Col2 containing the values ​​of Col1 :

 +---------+ | NewCol | |---------| | 123abc | | 234def | +---------+ 

How can i do this?

Here is a pseudo query to explain a bit.

 SELECT Col2 FROM TableName WHERE Col2 LIKE Col1; 

Answer 1

Use LOCATE()

 WHERE LOCATE(Col1, Col2); 

Returns a nonzero value if Col1 contained in Col2 .

Update

Note that an empty substring is always contained in another line, so in this case you need a different condition:

 WHERE LENGTH(Col1) AND LOCATE(Col1, Col2); 

Answer 2

 SELECT Col2 FROM TableName WHERE Col2 in(SELECT Col1 from TableName); 

Answer 3

 SELECT Col2 FROM TableName WHERE Col2 LIKE Concat("%",Col1,"%");