SQL LIKE clause
In SQL, we have three types of operators. Among them, we have Logical Operators which are used for logical operations. LIKE is one of the logical operators. It is used in the condition in SQL query with the WHERE clause. LIKE operator compares data with an expression using wildcard characters to match the pattern given in the condition. It can only be applied to a string value.
SQL Wildcard Characters
A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the SQL LIKE operator. Different databases use different wildcard characters. The following table illustrates different wildcard characters with descriptions, example, and which database use it.
Syntax of LIKE operator:
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;
Sample Table
Let us use the below Customers table to perform the LIKE operator.
Now, let us look at different examples of the LIKE operator using wildcard characters.
Using % character
Example 1:
The below SQL query selects all customers with a name starting with "a". In this case, the % operator is always placed at the end.
SELECT * FROM Customers
WHERE name LIKE 'a%';
Output:
Example 2:
The below SQL query selects all customers with a name ending with a. In this case, the % operator is always placed at the start.
SELECT * FROM Customers
WHERE name LIKE '%a';
Output:
Example 3:
The below SQL query selects all customers with a name that have "or" in any position. In this case, the % operator is on either end.
SELECT * FROM Customers
WHERE name LIKE '%or%';
Output:
Example 4:
The below SQL query selects all customers with a name that starts with "a" and ends with "o". In this case, the % operator is always placed in the middle
SELECT * FROM Customers
WHERE name LIKE 'a%o';
Output:
Example 5:
The below SQL query selects all customers with a name that does NOT start with "a". It uses the NOT operator.
SELECT * FROM Customers
WHERE name NOT LIKE 'a%';
Output:
Using _ character
Example 1:
The below SQL query selects all customers with a City starting with any character, followed by "_ondon".
SELECT * FROM Customers
WHERE city LIKE '_ondon';
Output:
Example 2:
The below SQL query selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on".
SELECT * FROM Customers
WHERE city LIKE 'L_n_on';
Output:
Using % and _ character
Example 1:
The below SQL query selects all customers with a name that have "r" in the second position.
SELECT * FROM Customers
WHERE name LIKE '_r%';
Output:
Example 2:
The below SQL query selects all customers with a name that starts with "a" and is at least 3 characters in length.
SELECT * FROM Customers
WHERE name LIKE 'a__%';
Output:
Conclusion
In this article, we discussed the LIKE operator of SQL. We also learned various SQL wildcards operators used by different databases. Note that all databases do not support all wildcard characters, so make sure to choose the right character from the above-given table.
