Introduction
SQL Like operator is used in SQL queries to search a specific pattern. We must use a 'WHERE' clause in the 'Like' operator. For a faster and more effective search, we use wildcard characters.
There are two of the following types of wildcard characters available in SQL.
- % (percentage) operator
- _ (underscore) operator
Using the above characters, there are two more wildcard characters available : [charlist] and [^charlist] or [!charlist].
SQL Like Operator
Before going into the details of wildcard characters, we should get some brief knowledge of the 'Like' operator.
Like Wildcard Syntax
SELECT */column_name from table_name where column_name like pattern;
There are different types of patterns available in the Like operator. Let us see the below table.
Demo_table
| Id |
State_Name |
City_Name |
| 1 |
Odisha |
Bhubaneswar |
| 2 |
West Bengal |
Kharagpur |
| 3 |
Odisha |
Cuttack |
| 4 |
West Bengal |
Howrah |
| 5 |
Odisha |
Bhadrakh |
| 6 |
Odisha |
Balasore |
| 7 |
Odisha |
Koraput |
| 8 |
Odisha |
Kendrapad |
| 9 |
West Bengal |
Kolkata |
The above table is named 'Demo_table.' We will use the above table to fetch data using the 'like' operator.
Different types of wildcard patterns
Using the % wildcard
select City_Name from Demo_table where City_Name LIKE 'k%';
The above statement will select all City_Name starting with 'k'.
Answer. Kharagpur, Koraput, Kendrapad, Kolkata
select City_Name from Demo_table where City_Name LIKE '%r';
The above statement will select all City_Name ending with 'r'.
Answer. Bhubaneswar, Kharagpur
select City_Name from Demo_table where City_Name LIKE '%tt%';
The above statement will select all City_Name having characters before and after 'tt'.
Answer. Cuttack
select City_Name from Demo_table where City_Name NOT LIKE '%tt%';
The above statement will select all City_Name having characters before and after 'tt'.
Answer. Except for Cuttack, it will return all the City_Name from the table.
Using the _ wildcard
select City_Name from Demo_table where City_Name LIKE '_hubaneswar';
The above statement will select all the City_Name starting from any character, followed by "hubaneswar".
Answer. Bhubaneswar
select City_Name from Demo_table where City_Name LIKE 'H_w_ah';
The above statement will select all the City_Name starting from 'H,' followed by any character, followed by 'w', followed by any character, followed by "an".
Answer. Howrah.
Using the [!charlist] Wildcard
select State_Name from Demo_table where City_Name LIKE '[!O]%';
The above statement will select all the State_Name not starting from the character 'O'.
Answer. West Bengal
select City_Name from Demo_table where City_Name LIKE '[!KB]%';
The above statement will select all the City_Name not starting from the character 'K' and 'B'.
Answer. Howrah, Cuttack
We can write the above query in the following format,
select State_Name from Demo_table where City_Name NOT LIKE '[O]%'
select City_Name from Demo_table where City_Name NOT LIKE '[KB]%';
select State_Name from Demo_table where City_Name LIKE '[^O]%'
select City_Name from Demo_table where City_Name LIKE '[^KB]%';
Using the [charlist] Wildcard
select City_Name from Demo_table where City_Name LIKE '[HC]%';
The above statement will select all the City_Name starting from the character 'H' and 'C'.
Answer. Howrah, Cuttack
If we have a particular range of characters like 'O' to 'X'.
Then we can write the query in the following format,
select State_Name from Demo_table where State_Name LIKE '[O-X]%';
The above statement will select all the State_Name starting from the character 'O' to 'X'
Answer. Odisha, West Bengal.
Summary
In this session, I discussed the SQL Like and Wildcard characters. I have implemented some examples, also. I hope this session will help beginners.
Thanks for reading this; happy to help you.