How to Use MySQL LIKE Operator for Multiple Values Effectively?

Using MySQL Like Operator for Multiple Values

Issue:

A MySQL query attempting to use the LIKE operator for matching values containing either "sports" or "pub" is not producing the desired results.

Query:

SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')

Expected Behavior:

The query should return rows where the "interests" field contains either "sports" or "pub" or both.

Explanation:

The issue arises because the LIKE operator with multiple patterns requires OR conditions. The provided query attempts to use two consecutive LIKE operators without proper OR logic, leading to incorrect matching.

Solution 1: OR Condition

A more straightforward solution is to use the OR operator to specify the multiple matching criteria:

SELECT * FROM table WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

Solution 2: REGEXP Operator

An alternative approach is to use the REGEXP (regular expression) operator, which supports pattern matching using a single expression:

SELECT * FROM table WHERE interests REGEXP 'sports|pub'

This expression uses the | (pipe) character to specify a logical OR between the two keywords, resulting in a single pattern that matches either "sports" or "pub" or both.

Note that the REGEXP operator is more efficient for matching multiple patterns compared to using LIKE with OR conditions.

The above is the detailed content of How to Use MySQL LIKE Operator for Multiple Values Effectively?. For more information, please follow other related articles on the PHP Chinese website!