SQL Like Clause – Syntax and Example

In this tutorial, we will learn about the LIKE logical operator which is used for the wildcards in SQL.

Let us now move ahead and understand the syntax and uses of the LIKE operator and then move on to the examples of the same.

What is LIKE Operator in SQL?

LIKE operator is used for operating the wildcards on our database. We use SQL wildcards when we need to search for complex data. This complex data could comprise strings or numerical with special characteristics.

Wildcards also come in handy when we want to speed up our querying process. The results are considerably fast when wildcards are in use. We have already seen SQL Wildcards are put up with the LIKE operator.

Uses of LIKE Operator in SQL

Some of the uses of the LIKE operator are as follows:

• Allows us to extract data that matches the required pattern.
• Helps us to perform complex regex-based queries on our data.
• Simplifies the complex queries.

Rules of SQL Like Operator

Two main rules of Like Operator are as follows:

• [ % ] – Represents zero, one, or multiple characters.
• [ _ ] – Represents one single character.

We can go through the following table to understand the Like operator in further details.

Sr. No	Usage with LIKE operator	Description
1	WHERE columnName LIKE ‘a%’	Returns values which start with ‘a’.
2	WHERE columnName LIKE ‘%a’	Returns values which end with ‘a’.
3	WHERE columnName LIKE ‘%ab%’	Returns any value that has ‘ab’ in any position.
4	WHERE columnName LIKE ‘_a%’	Returns any value which has ‘a’ as the second character.
5	WHERE columnName LIKE ‘a_%_%’	Returns any value that starts with ‘a’ and is at least 3 characters in length.
6	WHERE columnName LIKE ‘a%b’	Returns any value which starts with ‘a’ and ends with ‘b’.

Syntax of SQL Like Operator

LIKE operator follows the following syntax:

SELECT col1 as alias1,
Col2 as alias2,
Col3 as alias3,
……. FROM tableName
WHERE col1 LIKE “pattern”;

Here the pattern is the regex expression that we want to be followed by the result of our query.

Demo Database

Let us now have a look at the example database of DataFlair which we will be using all along with this tutorial.

Query:

USE DataFlair;
SELECT * FROM Dataflair;

Output:

Let us now move forward with examples of the LIKE operator.

Example 1: Let us try to find the details of all the employees having ‘R’ as the first character in their names.
Query:

USE DataFlair;
SELECT * FROM Dataflair
WHERE name LIKE 'R%';

Output:

Example 2: Let us try to find the details of all the employees having ‘i’ as the last character in their names.
Query:

USE DataFlair;
SELECT * FROM Dataflair
WHERE name LIKE '%i';

Output:

Example 3: Let us try to find the details of all the employees located at offices which have ‘n’ as a character in them.
Query:

USE DataFlair;
SELECT * FROM Dataflair
WHERE location LIKE '%d%';

Output:

 

Example 4: Let us try to find the details of all the employees having ‘a’ as the second character in their names and name is 4 characters long.
Query:

USE DataFlair;
SELECT * FROM Dataflair
WHERE name LIKE '_a%__%';

Output:

Example 5: Let us try to find the details of all the employees having names starting and ending with ‘N’.
Query:

USE DataFlair;
SELECT * FROM Dataflair
WHERE name LIKE 'n%N';

Output:

Summary

In this tutorial, we have seen everything about the SQL LIKE operator.

We started with what a Like operator is and what its use cases are. We learnt the regex-like properties of the LIKE operator.

Further we learned the syntax and the rules of the LIKE operator and after this, we moved on to our examples with our exemplar database – DataFlair.

We have seen how the like operator functions with the wildcards and how we can use it. With the help of the Like operator, we can easily reduce the complexity of our queries.