How to Efficiently Match Multiple Words in MySQL Using LIKE or REGEXP?

Multi-Word Matching in MySQL Using SELECT LIKE or REGEXP

The SELECT statement in SQL allows for filtering records based on specific criteria. When dealing with multi-word data, such as product names or descriptions, it can be challenging to find records that match multiple words.

LIKE vs. REGEXP

MySQL provides two operators that can be used for wildcard matching: LIKE and REGEXP. LIKE uses the % wildcard to match zero or more characters, while REGEXP allows for more complex regular expression matching.

Matching Multiple Words with LIKE

In the given example, the query SELECT name FROM table WHERE name LIKE '%Stylus 2100%' does not return any results because it doesn't match both words in the record. To achieve multi-word matching with LIKE, you can use the following approach:

SELECT name FROM `table` WHERE `name` LIKE '%Stylus%' AND `name` LIKE '%2100%'

This query will select records that contain both "Stylus" and "2100" in any order.

Matching Multiple Words with REGEXP

REGEXP offers a more powerful way to match multiple words by using regular expressions. The following query uses a regular expression to match records that contain "Stylus" followed by one or more characters followed by "2100":

SELECT name FROM `table` WHERE `name` REGEXP 'Stylus.+2100'

This query will return the record with the value "Stylus Photo 2100".

The above is the detailed content of How to Efficiently Match Multiple Words in MySQL Using LIKE or REGEXP?. For more information, please follow other related articles on the PHP Chinese website!