SQL Like Multiple Values

July 3, 2022 | by Arround The Web | No comments

“The SQL LIKE operator allows performing logical evaluation for any matching records. Using the LIKE operator, you can specify single or multiple conditions. This allows you to perform an action such as select, delete, and updating any columns or records that match the specified conditions. It is mainly paired with a where clause to set the conditions.

In this tutorial, we will look at the workings of the LIKE clause and how we can use it to specify multiple conditions.”

Sample Data

For this tutorial, we are going to use a simple database with minimal data. The full query to create the data is as shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

DROP DATABASE IF EXISTS simple_shop;

CREATE DATABASE simple_shop;

USE simple_shop;

CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price_per_item INT,
stock INT NOT NULL,
supported BOOL NOT NULL
);

INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Wireless Earbuds', 59.23, 100, TRUE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Energy Drinks', 5.0, 500, TRUE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Scouts Knife', 129.35, 70, FALSE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Rubber Shoes', 45.44, 700, TRUE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Gasoline', 14.35, 70, FALSE);
INSERT INTO products (product_name, price_per_item, stock, supported)
VALUES('Explosives', 129.35, 50, TRUE);
SELECT
*
FROM
products;

The code should create a table as shown:

SQL Like Example #1

Let us now look at various examples of the SQL LIKE operator.

Suppose we wish to fetch the records where the product name start’s with “E”, we can use the query:

1
2
3
4
5
6

SELECT
*
FROM
products
WHERE
product_name LIKE "E%";

The code above uses the LIKE operator to match a specific pattern. The resulting table is as shown:

SQL Like Example #2

In other cases, we want to fetch records where a specific character is equal to a specific value. For example, to fetch the record where the second character is “x”, we can run the code:

1
2
3
4
5
6

SELECT
*
FROM
products
WHERE
product_name LIKE "_x%";

The code above should return:

SQL Like Multiple Conditions

We can specify multiple conditions in the LIKE operator using SQL’s logical operators. The code syntax is as shown below:

1
2
3
4
5
6
7
8
9

SELECT
col1,
col2,
col2...colN
WHERE (column_name LIKE 'pattern'
OR column_name LIKE 'pattern'
OR column_name LIKE 'pattern')
FROM
TABLE_NAME;

An example is as shown:

1
2
3
4
5
6
7

SELECT
*
FROM
products
WHERE (product_name LIKE "e%"
OR product_name LIKE 'w%'
OR product_name LIKE '__s%');

In the above code, we use the OR operator to specify multiple conditions using the OR operator.

The resulting table should return:

Only the records that match either of the specified conditions are returned.

Conclusion

Through this article, you learned how to use the SQL LIKE operator to match the records match a condition. You also learned how to specify multiple conditions in the LIKE operator.

Thanks for reading!!

Source: linuxhint.com