How Can I Find MySQL Rows Contained Within a Specific String?

Finding Rows Containing a Query String: The Reverse of LIKE Operator

In MySQL, the LIKE operator is commonly used to search for rows that contain a specified text pattern. For instance, a query like:

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

would return records such as "John Smith" and "Peter Johnson." However, what if you need to do the opposite operation? That is, find rows that themselves contain a given string within them.

Achieving the Reverse of LIKE

To find rows that are contained within a query string, you can utilize a combination of the LIKE operator and the CONCAT() function. Here's an example:

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

In this query, the CONCAT() function joins a wildcard character (%) with the name field to create a pattern:

%name%

The LIKE operator then matches this pattern against the provided string, identifying all rows that contain names found within it. So, in this case, it would retrieve "John Smith" and "Peter Johnson."

This technique allows you to search for rows that are contained within a larger string, effectively reversing the functionality of the LIKE operator and providing a flexible way to find specific data within a database.

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