What is the WHERE clause?

The WHERE clause filters the result data stored in the database (in the form of tables). It is used to extract only those result data that fulfill the given condition with the WHERE clause.Its basic syntax is

Table of contents

Introduction

Syntax

LIKE Clause Examples

Frequently Asked Questions

Key Takeaways

Last Updated: Mar 27, 2024

LIKE Clause in SQL

Author Prashant Singh

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

The SQL LIKE clause is a logical operator used to determine if a character string matches a specified pattern in the given data. The LIKE operator( or clause) is used in a WHERE clause to search for a specified pattern in column data.

Following are the two wildcards often used in conjunction with the LIKE operator:

The percent sign (%) :
The percent sign represents any string of zero, one or more characters.
The underscore sign (_):
The underscore sign represents any single character.

Some other wildcards are as follows.

The [List of characters] wildcard represents any character within the specified set.
The [character-character] wildcard represents any character within the given specified range.
The [^]: This represents any single character, not within a list or a range.

The percent and the underscore sign can also be used in combinations in SQL queries. A wildcard character is used to substitute one or more characters in a string.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Syntax

The basic syntax of the LIKE clause is as follows.

SELECT column1,column2,...

FROM table_name

WHERE column LIKE pattern;

Pattern

The pattern is a sequence of characters that need to be searched in the column data. The pattern can include the wildcard characters mentioned above.

Note: We can also combine any number of WHERE conditions using AND or OR operators.

LIKE Clause Examples

The following are some LIKE operators with “%” and “_” wildcards.

LIKE Operator Command	Description
WHERE UserName LIKE ‘c%’	It gives any values that start with “c”.
WHERE UserName LIKE ‘%c’	It provides any values that end with “c”.
WHERE UserName LIKE ‘%an%’	It gives any values that have “an” in any position.
WHERE UserName LIKE ‘_a%’	It gives any values that have “a” in the second position.
WHERE UserName LIKE ‘b_%’	It provides any values that start with “b” and are at least of the length of two characters.
WHERE UserName LIKE ‘b__%’	It gives any values that start with “b” and are at least three characters in length.
WHERE UserName LIKE ‘b___%’	It gives any values that start with “b” and are at least four characters in length.
WHERE UserName LIKE ‘a%n’	It gives any values that start with “a” and end with “n”.

For example, consider the USERS table having records given below.

UserID	UserName	Age	Location	Stipend
1	Nayan	22	Kolkata	20000.00
2	Sejal	21	Kota	15000.00
3	Kajal	24	Gwalior	10000.00
4	Neeta	22	Pune	12000.00
5	Ayush	21	Allahabad	15500.00
6	Rajesh	26	Surat	25000.00
7	Latika	25	Delhi	18000.00

The following example displays all the records from the USERS table where Stipend starts with 150.

SELECT * FROM USERS

WHERE Stipend LIKE ‘150%’;

The result of the above command would be

UserID	UserName	Age	Location	Stipend
2	Sejal	21	Kota	15000.00

The following example gives all the records from the USERS table where UserName starts with “N”.

SELECT * FROM USERS

WHERE UserName LIKE ‘N%’;

This would display the following result.

UserID	UserName	Age	Location	Stipend
1	Nayan	22	Kolkata	20000.00
4	Neeta	22	Pune	12000.00

The following example gives all the records from the USERS table where Location starts with “Ko” and has a minimum of four characters.

SELECT * FROM USERS

WHERE Location LIKE ‘Ko__%’;

This would display the following result

UserID	UserName	Age	Location	Stipend
1	Nayan	22	Kolkata	20000.00
2	Sejal	21	Kota	15000.00

The following example gives all the records from the USERS table where UserName ends with “sh”.

SELECT * FROM USERS

WHERE Location LIKE ‘%sh’;

This would display the following result

UserID	UserName	Age	Location	Stipend
5	Ayush	21	Allahabad	15500.00
6	Rajesh	26	Surat	25000.00

The following example gives all the USERS table data where UserName starts with “L” and ends with “a”.

SELECT * FROM USERS

WHERE Location LIKE ‘L%a’;

This displays the following result

UserID	UserName	Age	Location	Stipend
7	Latika	25	Delhi	18000.00

The following example gives all the records from the USERS table where UserName does not start with “N”.

SELECT * FROM USERS

WHERE Location NOT LIKE ‘N%’;

This displays all the users whose UserName does not start with N.

UserID	UserName	Age	Location	Stipend
2	Sejal	21	Kota	15000.00
3	Kajal	24	Gwalior	10000.00
5	Ayush	21	Allahabad	15500.00
6	Rajesh	26	Surat	25000.00
7	Latika	25	Delhi	18000.00

Also See, sql functions and SQL EXCEPT

Frequently Asked Questions

What is the WHERE clause?
The WHERE clause filters the result data stored in the database (in the form of tables). It is used to extract only those result data that fulfill the given condition with the WHERE clause.
Its basic syntax is

SELECT * FROM table_name WHERE condition;

2. What is a pattern in the LIKE clause?
The pattern is a sequence of characters that need to be searched in the column data.

3. What is the SELECT statement in the SQL command?
The SELECT statement in SQL is used to select data from a database. Its syntax is as follows-

SELECT expression From table_name;

Here “expression” can be column names etc.
If we want to select all fields in the table, we can use the following statements.

SELECT * From table_name ;

Key Takeaways

In this blog, we learn how to use the LIKE clause in SQL with the WHERE clause to get the data with some specific pattern in the database using SQL queries.

We begin with learning the introduction of the LIKE clause in SQL, some example wildcards, the syntax of this statement, and how to use them in queries with various examples.