How to Effectively Use MySQL\'s LIKE and REGEXP to Search for Multiple Words in a Single Field?

Using MySQL SELECT to Match Multiple Words in a Single Record: LIKE vs REGEXP

Considering a table with a field named name containing the entry 'Stylus Photo 2100', querying with SELECT name FROM table WHERE name LIKE '%Stylus 2100%' returns no results, while searching for 'Photo 2100' does. The task is to retrieve the record using 'Stylus 2100'.

Using REGEXP

If the order of words is known, the REGEXP operator can be employed. Using SELECT name FROM table WHERE name REGEXP 'Stylus. 2100', where . matches any character sequence between Stylus and 2100.

Using LIKE

For cases where the order may vary, a combination of LIKE can be applied. By using SELECT name FROM table WHERE name LIKE '%Stylus%' AND name LIKE '!00%', both conditions are checked independently, ensuring that the record is retrieved.

The above is the detailed content of How to Effectively Use MySQL\'s LIKE and REGEXP to Search for Multiple Words in a Single Field?. For more information, please follow other related articles on the PHP Chinese website!