In the previous article
SQL For Beginners - Operators, we learned about the various types of operators of SQL. In this article, we will learn about two very important operators/predicates of SQL: the LIKE and IN clause.
LIKE Clause
The LIKE Clause helps us to achieve pattern matching. There are situations where we need to get some particular data/record based on a particular pattern. The LIKE Clause helps in comparing the values in the table with the specified pattern and return the records based on it.
In order to perform pattern matching, we can use four wildcards ( % , _ , [], [^] ) along with the LIKE Clause.
The % (percent) sign allows us to match a string of any length. While the _ (underscore) sign is used to represent a single character.
Syntax:
SELECT columnList FROM table_Name
WHERE columnName LIKE pattern;
Example:
Select * from Students where StudentName LIKE 'S%';
In this query, you can see that the pattern is S%. This means that we want the details of the Students from the Students table whose name starts with S followed by any number of characters.
As we can see in the output below, we get Soy and Sam.
Now, suppose we need the details of the Students whose name ends with alphabet 'm'. We will write the following query to achieve this:
Select * from Students where StudentName LIKE '%m';
Here, we get Tom and Sam.
Going ahead, suppose that we need the details of the Students whose name contains alphabet 'a'. 'a' can be anywhere in the name. In the beginning, in the end, or somewhere in the middle too.
This can be done by writing the following query:
Select * from Students where StudentName LIKE '%a%';
Here, we get the details of the students containing alphabet 'a' in the name of the students.
Now, let us see the use of underscore wildcard.
Select * from Students where StudentName LIKE 'P_t_r';
We get Peter in the output. This is because of the pattern 'P_t_r'. It means the first character should be P. Then, we have an underscore which means that the second character can be anything. Then we have t which means that the third character should be t. The alphabet 't' is again followed by an underscore which means that after 't', there can be any character and the last character should be 'r'.
Select * from Students where City LIKE '[A-D]elhi';
Here, [A-D] indicates that the first letter can be A,B,C or D. And, the first letter should be followed by 'elhi'. In our case, we will get the details of the Student who belongs to Delhi.
Similarly, we can use [^] for specifying that the letters should not be the one specified within the brackets.
Select * from Students where City LIKE '[^A-D]elhi';
Now, this query will check the cases where the first alphabet in the string should not be A,B,C or D and should be followed by 'elhi'. We don't have any City with such pattern in its name. Hence, this query will return nothing.
IN Clause:
At times, we need to compare multiple values in the where clause. Suppose that we need to fetch details of the Students who belong to Pune and Delhi both. Here, we need to specify both the cities. This can be done using the IN Clause in SQL.
Select * from Students where City IN ('Pune','Delhi');
Suppose, if we write the NOT operator before the IN Clause, then it will do exactlythe opposite of that of the IN Clause.
Select * from Students where City NOT IN ('Pune','Delhi');
As seen in the output, we get the details of the students who do not belong to Pune and Delhi. This was a brief overview of the LIKE and IN clause in SQL.
For more articles like this, stay tuned. Keep Learning!