How Can I Find MySQL Rows Whose Values Are Contained Within a Given Text String?

Finding Row Values Contained Within a Query Text in MySQL

MySQL's LIKE operator excels at locating rows containing specified query text. Consider this example:

SELECT name FROM user WHERE name LIKE "%john%"

Rows like John Smith and Peter Johnson will be returned.

But what if we seek the opposite, rows whose values exist within a query text? For instance, providing "John Smith and Peter Johnson are best friends" and finding all names from the table present in that string. How can we accomplish this?

Reverse LIKE Operation

To perform this reverse LIKE operation, utilizing the CONCAT() function is key:

SELECT name FROM user 
WHERE 'John Smith and Peter Johnson are best friends' LIKE
  CONCAT('%', name, '%')

The CONCAT() function concatenates strings, in this case, wrapping each row's name in percent signs ("%"). This allows the LIKE operator to check if the entire query text contains each name.

For example, if the user table includes "John" and "Peter" names, the query will return:

| name |
| --- |
| John |
| Peter |

This technique effectively identifies rows that match a specific text, inverting the traditional LIKE behavior.

The above is the detailed content of How Can I Find MySQL Rows Whose Values Are Contained Within a Given Text String?. For more information, please follow other related articles on the PHP Chinese website!