LIKE Clause in SQL

PRASHANT SINGH
Last Updated: May 13, 2022

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.

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 CommandDescription 
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

 

Frequently Asked Questions

  1. 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.

Visit here to learn more about different topics related to database management systems.

Also, try CodeStudio to practice programming problems for your complete interview preparation.

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Was this article helpful ?
2 upvotes

Comments

No comments yet

Be the first to share what you think