How Can I Efficiently Use Multiple Conditions with SQL's LIKE Operator?

Multiple conditions for efficient use of SQL LIKE operator

The LIKE operator in SQL is used to search for strings containing specified patterns. Usually, we append wildcard characters like % to the match pattern. However, if we want to introduce multiple conditions using LIKE, the traditional OR method may not be the best choice.

Alternative: Temporary table

A better way to introduce multiple conditions in the LIKE operator is to create a temporary table. An example is as follows:

<code class="language-sql">CREATE TEMPORARY TABLE patterns (
  pattern VARCHAR(20)
);

INSERT INTO patterns VALUES ('ABC%'), ('XYZ%'), ('PQR%');

SELECT t.*
FROM tbl t
JOIN patterns p ON (t.col LIKE p.pattern);</code>

In this example, we create a temporary table named patterns to store multiple matching patterns. We then insert the required schema into this table. Finally, we perform a JOIN operation between the tbl table and the patterns table to find matching rows based on the pattern column.

Unique match

In the above example, we assume that the pattern is unique and each row in the tbl table matches only one pattern. However, if there are multiple match possibilities, we can use the DISTINCT query modifier to ensure unique results.

<code class="language-sql">SELECT DISTINCT t.*
FROM tbl t
JOIN patterns p ON (t.col LIKE p.pattern);</code>

With this modification, we guarantee that each row in the tbl table only appears once in the results, even if it matches multiple patterns.

The above is the detailed content of How Can I Efficiently Use Multiple Conditions with SQL's LIKE Operator?. For more information, please follow other related articles on the PHP Chinese website!