In our day-to-day lives, we deal with a large amount of data with a high possibility of inconsistency. So, to filter out data based on a string and specific pattern, SQL provides a flexible way. In this tutorial, we will learn about the LIKE and NOT Like Operators in SQL, which perform pattern matching within string data.
SQL LIKE Operator
The SQL LIKE is a logical operator that verifies whether a string contains a specified pattern or not. It is used in the WHERE clause with SELECT, DELETE and UPDATE to filter records based on patterns. It is commonly used with text data types such as CHAR and VARCHAR.
A pattern compromise of:
- Regular Character: Characters that should perfectly match the provided characters.
- Wildcard Characters: Characters that match with the random part of the string.
Syntax:
SELECT column01, column02, ...
FROM table01
WHERE column01 LIKE 'value';Code language: SQL (Structured Query Language) (sql)Example of SQL LIKE Operator
Let’s say we have a table named “Information”, which comprises the user ID, first name, last name and their age.
Now, use the LIKE Operator to fetch the record of people with the LastName “Arora”.
SELECT FirstName, LastName, age
FROM Information
WHERE LastName LIKE 'Arora';Code language: SQL (Structured Query Language) (sql)Output:
This is a primary use of LIKE Operators.
We can also fetch the same with the following:
SELECT FirstName, LastName, age
FROM Information
WHERE LastName = 'Arora';Code language: SQL (Structured Query Language) (sql)Output:
Note: The equal(=) is the comparison operator, which operates on strings and numbers. The equal operator is used to check equality. On the other hand, the LIKE operator is used to match strings. They are not the same.
SQL LIKE With Wildcards
The LIKE operators allow us to perform pattern matching using the wildcard characters.
Now, well, look at the given wildcard characters:
- The percent(%) wildcard matches any string of zero or more characters.
- The underscore(_) wildcard matches any string of single characters.
Also Read: MySQL ANY and ALL Operators!
SQL LIKE With Underscore(_) Wildcard
Example 1:
Let’s apply the underscore wildcard to our “Information” table where we want to fetch the record of the user whose FirstName must start with “R”, the third letter of FirstName should be “m”, and the middle word could be anything.
SELECT FirstName
FROM Information
WHERE FirstName LIKE 'R_m';Code language: SQL (Structured Query Language) (sql)Output:
From the above example, we can see how the result set contains the pattern we specified.
Example 2:
Now, let’s look at another example where we will check the LastName of the users, which starts with “P” and ends with “Y”, the middle four words could be anything.
SELECT FirstName,LastName
FROM Information
WHERE LastName LIKE 'P____y';Code language: SQL (Structured Query Language) (sql)Output:
SQL LIKE With Percent(%) Wildcard
Example 1:
Let’s apply the Percent wildcard to our “Information“ table where we want to fetch the records of the users whose LastName ends with “ra”.
SELECT FirstName,LastName
FROM Information
WHERE LastName LIKE '%ra';Code language: SQL (Structured Query Language) (sql)Output:
In the above example, we can see that all the records of users whose LastName contains “ra” are fetched.
Example 2:
Now, if we want to find the pattern embedded in the middle, you can add ‘%’ to the start and the end. Let’s look at the example below where we are fetching the records of the users whose FirstName contains “am” in the middle.
SELECT FirstName
FROM Information
WHERE FirstName LIKE '%am%';Code language: SQL (Structured Query Language) (sql)Output:
SQL LIKE With Percent(%) & Underscore(_) Wildcard
Let’s join the percentage(%) and underscore(_).
Example:
Let’s say we want to fetch the records of the users whose FirstName third word is “m”.
SELECT FirstName,LastName
FROM Information
WHERE FirstName LIKE '__m%';Code language: SQL (Structured Query Language) (sql)Output:
SQL LIKE With Multiple Values
The SQL LIKE operator can also be used with multiple values to match various patterns in a single command. When we need to filter data inside a single column based on multiple criteria, this approach is very effective.
Syntax:
SELECT * FROM table01
WHERE column LIKE 'value01' OR column LIKE 'value02';Code language: SQL (Structured Query Language) (sql)Example:
Let’s fetch the records of the users whose FirstName starts with either “G” or the LastName ends with “a”. To do that, we will use the below code.
SELECT * FROM Information
WHERE FirstName Like 'G%' OR LastName LIKE '%a';Code language: SQL (Structured Query Language) (sql)Output:
The above command checks for two conditions: It fetched the rows where the FirstName column starts with “G” or the LastName column ends with “a”.
SQL NOT LIKE Operator
The SQL NOT LIKE is a logical operator that checks whether a string does not contain a specified pattern. It is used in the WHERE clause with SELECT, DELETE and UPDATE to filter records based on patterns that are not matched.
Syntax:
SELECT column01, column02, ...
FROM table01
WHERE column NOT LIKE 'value';Code language: SQL (Structured Query Language) (sql)Example of SQL NOT LIKE Operator
Let’s fetch the records of the users whose LastName is not “Arora”. To do that, we will use the below code.
SELECT FirstName, LastName, age
FROM Information
WHERE LastName NOT LIKE 'Arora';Code language: SQL (Structured Query Language) (sql)Output:
As we can see, NOT LIKE excluded all the specified patterns, which means fetching all the records whose LastName is not “Arora”.
Read More: MySQL IS NOT EQUAL TO Operator!
Conclusion
In this tutorial, we discussed the LIKE and NOT LIKE SQL operators and covered how and when to use the underscore and percentage wildcards with multiple examples. We also saw how to use the SQL LIKE operator with multiple values. We hope you find this article informative and enjoyable.