Compare 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 retrieve all the entries in Col2 that contained the values of Col1:

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

How would I go about that?

Here is a pseudo-query to explain a bit further.

SELECT Col2 FROM TableName WHERE Col2 LIKE Col1;

Answer 1

Use LOCATE()

WHERE LOCATE(Col1, Col2);

It returns a non-zero value if Col1 is contained within Col2.

Update

Note that an empty substring is always contained within another string, so in this case you need another 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,"%");