SQL LIKE Query

Introduction to SQL LIKE Query

SQL LIKE Query is used in the where clause to find the specified column data from a table. It will determine that the specified string used in the Query will match the specified pattern. We can include patterns like wildcard and regular characters. Regular characters will match the specified string used in our Query when matching the pattern.

Overview of SQL LIKE Query

We use it in the select Query most of the time to find the records as per the specified pattern we have used in the Query.

We are using sql like queries with different types of wildcard operators. We use the following wildcard operator by using sql like queries.

• Percentage (%): This type of wildcard operator represents the one, zero, or multiple characters.
• Underscore (_): This type of wildcard operator will represent only a single character. It will accept only a single character we have used in our pattern.
• []: This type of wildcard operator single character from the specified range of values.
• ^: This type of wildcard operator is used in a single character that we have not defined in the list of ranges.

Below is the syntax of SQL like by using a select query.

Syntax:

Select name_of_column from name_of_table where name_of_column like ‘xxx%’;

Select name_of_column from name_of_table where name_of_column like ‘%xxx%’;

Select name_of_column from name_of_table where name_of_column like ‘%xxx’;

Select name_of_column from name_of_table where name_of_column like ‘xxx_’;

Select name_of_column from name_of_table where name_of_column like ‘_xxx_’;

Select name_of_column from name_of_table where name_of_column like ‘_xxx’;

Below is the syntax by using the delete query.

Syntax:

Delete from name_of_table where column_name like ‘xxx%’;

Delete from name_of_table where column_name like ‘%xxx%’;

Delete from name_of_table where column_name like ‘%xxx’;

Delete from name_of_table where column_name like ‘xxx_’;

Delete from name_of_table where column_name like ‘_xxx_’;

Delete from name_of_table where column_name like ‘_xxx’;

Below is the syntax by using the update query as follows.

Syntax:

Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘xxx%’;

Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘%xxx%’;

Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘%xxx’;

Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘xxx_’;

Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘_xxx_’;

Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘_xxx’;

In the above syntax, we have defined SQL like select, update, and delete queries. Select, delete and update Query is very easy to use SQL compared to the = and != comparison operator.

It is valuable and essential to find specified pattern records from the table.

Using SQL LIKE Query

We can use it to find the matched string by using a specified pattern.

• We can use it to select, delete, and update specified records specified in the pattern.
• Suppose we want to delete a specified pattern of records from the table, then it is the best choice compared to the = and != operator.
• Suppose we want to select a specified pattern of records from the table, then it is the best choice compared to the = and != operator.
• Suppose it with the conjunction of where clause with select, delete and update statement.

In the following example, we use a select query with sql like.

In the below example, we can see that we have used a select query with the like condition as %B%; it will display all the results in which the middle letter contains B.

Code:

select * from like_query where name like '%B%';

Output:

The below example shows how we can use sql like queries.

In the following example, we use a delete query with sql like. In the below example, we can see that we have used a delete query with the like condition as %B%; it will delete all the results in which the middle letter contains B.

Code:

Delete from like_query where name like '%B%';
Select * from like_query;

Output:

In the following example, we use an update query with sql like.

In the below example, we can see that we have used an update query with the like condition as P%; it will update all the results that the middle letter contains P.

Code:

Update like_query set name = ‘ABC’ where name like 'P%';
Select * from like_query;

Output:

Examples of SQL LIKE Query

Different examples are mentioned below:

Example #1

Below is the example of SQL-like with select Query.

In the example below, we use the % and underscore wildcard operator.

Code:

select * from like_query where name like '%B%';
select * from like_query where name like 'A%';
select * from like_query where name like '%C';
select * from like_query where name like '_B_';
select * from like_query where name like '_BC';
select * from like_query where name like 'AB_';

Output:

Example #2

Below is the example of SQL-like with an update query.

In the example below, we use the % and underscore wildcard operator.

Code:

Update like_query set name = ‘PQR’ where name like 'A%';
Update like_query set name = ‘PQR’ where name like '%B%';
Update like_query set name = ‘PQR’ where name like '%C';
Update like_query set name = ‘PQR’ where name like 'AB_';
Update like_query set name = ‘PQR’ where name like '_BC';
Update like_query set name = ‘PQR’ where name like '_B_';
Select * from like_query;

Output:

Example #3

Below is the example of SQL-like with delete query.

In the example below, we use the % and underscore wildcard operator.

Code:

Delete from like_query where name like '%Q%';
Delete from like_query where name like '_Q_';
Select * from like_query;

Output:

Conclusion

We are using it in select update and delete Query. It is used where clause finds the specified column data from a table. It will determine that the specified string used in the Query will match the specified pattern.

Recommended Articles

This is a guide to SQL LIKE Query. We discuss the introduction, overview, and using SQL LIKE Query with examples. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. PL/SQL NOT EQUAL
4. SQL NOT IN