In this tutorial, you will learn how to use the LIKE operator in Standard SQL which allows you to check if a value is in a given set of values.
SQL IN Operator
The IN operator in SQL facilitates a quick search of a value in a given set without the need for complex computation. The function syntax is as follows:
The operator checks if the provided expression is located in the given values. If found, the operator returns TRUE; otherwise, it returns false.
In most cases, you will often pair the IN operator with other clauses such as the WHERE clause. This can allow you only to fetch the values that match a specific condition.
Let us discuss some basic example usage of the IN operator in SQL.
Example Usage:
The following examples illustrate how to use the IN operator in SQL.
Example 1: Basic Usage
This example shows the basic usage of the IN operator.
In this example, we use the IN operator to check if the “SQL” string is in the given set of values.
Example 2: Using the IN Operator in a Table
The following shows how to use the IN operator in conjunction with the WHERE clause in a table.
The table is as follows:
The query should return the matching records as follows:
Case Insensitive SQL LIKE Operator
Once thing you will notice about the LIKE operator is that it is case sensitive. This means that the values without a similar casing are considered not equal.
To perform a case-insensitive comparison, you can use the opposite of the LIKE operator which is called ILIKE.
The operator allows us to perform a case-insensitive comparison as shown in the following syntax:
FROM table_name
WHERE column_name ILIKE 'search_term%';
We can also use the lower() and upper() functions to convert the strings to one casing before performing the operation.
An example syntax is as follows:
FROM table_name
WHERE LOWER(column_name) = LOWER('search_term');
This should convert all values in the column to lowercasing which means that it match the lowercased string.
Conclusion
This article shows how to use the IN operator in Standard SQL. The IN operator allows you to check if a given expression is in a set of values.
