Role Of SQL Like And Wildcards

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.