CODEWITHSUNDEEP
mysqlsql
abaini01
abaini01

Reputation: 187

How to use LIKE clause to compare two columns in the same table?

I need to verify if the value of Column_1 is contained in the values of Column_2. The type of data in the 2 columns are:

ID   |   column1    |  column2           |
----------------------------
1   |   apple       |  pear,grape, apple |
2   |   pear        |  apple,grape       |
3   |   apple       |  apple             |

The query should return to me the lines 1 and 3.

I've tried something like this, but doesn't work:

SELECT * FROM `table` as C WHERE column_1 LIKE "%C.column_2%"

Upvotes: 1

Views: 20703

Answers (2)

Harrison O
Harrison O

Reputation: 1210

For those using eloquent, you could solve the problem as seen below:

table::whereRaw("column1 LIKE CONCAT('%', column2, '%')")

Upvotes: -1

Barmar
Barmar

Reputation: 780889

Your comparison is backwards. It should be:

WHERE column2 LIKE CONCAT('%', column1, '%');

Note that this will return a row like:

5 apple pineapple,grapefruit

If that's not appropriate, you shouldn't use LIKE. FIND_IN_SET is designed to match items in a column-delimited list, so you could use:

WHERE FIND_IN_SET(column1, column2)

However, make sure you don't have any spaces around the commas if you do this.

It would be much better if you normalized your table. Comma-delimited lists should not be used, you should use a many-to-many relation table.

Upvotes: 3

Related Questions