How Can I Reverse MySQL's LIKE Operator to Find Rows Contained Within a String?

Finding the Inverse of MySQL's LIKE Operator

The LIKE operator in MySQL enables the retrieval of rows containing specified text fragments. However, what if the requirement is to identify rows whose content matches a provided text string? This inverse operation, where rows are selected based on their containment within the query text, can be achieved using the following technique:

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

In this query, the name column from the user table is retrieved where the provided string, which contains the names being searched for, appears as a substring within the row values. The CONCAT function is used to enclose each row value with percentage signs, creating a wildcard pattern that enables the substring search.

For instance, if the user table contains the name "John Smith," it will be returned by the query because the provided string "John Smith and Peter Johnson are best friends" includes the name as a substring. This technique effectively performs the reverse operation of the LIKE operator, finding rows that are contained within the specified text rather than the text fragment being contained within the rows.

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