What does the SELECT statement mean?

The SELECT statement in SQL is basically used to select data from a given database. Its syntax is as follows-

Wildcard operators - Naukri Code 360

Introduction

The SQL supports the given two wildcard operators used with the LIKE operator to check if a given character string matches a specified pattern in the given data in the database. A wildcard operator is used to substitute one or more characters in a string.

The primary two wildcard operators that are often used 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 wildcard operators 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.

Note: We can also use the percent and the underscore sign in combinations in SQL queries. A wildcard operator is used to substitute one or more characters in a string.

To know more about LIKE Clause, you can refer here.

Syntax

The basic syntax of the wildcard operators used with the LIKE clause is as follows.

SELECT column1,column2,...

FROM table_name

WHERE column LIKE pattern_containing_wildcard_operators;

Pattern

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

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

LIKE Clause Examples Using Wildcard operators

The following are examples of the LIKE clause with “%” and “_” wildcards with their description.

LIKE Clause Command with Wildcard Operators	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 two characters in length.
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	Aman	22	Gurgaon	22000.00
2	Neha	21	Kota	18000.00
3	Uday	24	Noida	10000.00
4	Neetu	22	Pune	15000.00
5	Ajay	21	Kanpur	15800.00
6	Ramesh	26	Hyderabad	23000.00
7	Pratyush	25	Delhi	20000.00

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

SELECT * FROM USERS

WHERE Stipend LIKE ‘230%’;

The result of the above command would be.

UserID	UserName	Age	Location	Stipend
6	Ramesh	26	Hyderabad	23000.00

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

SELECT * FROM USERS

WHERE UserName LIKE ‘A%’;

This example would display the following result.

UserID	UserName	Age	Location	Stipend
1	Aman	22	Gurgaon	22000.00
5	Ajay	21	Kanpur	15800.00

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

SELECT * FROM USERS

WHERE Location LIKE ‘No__%’;

This example would display the following result.

UserID	UserName	Age	Location	Stipend
3	Uday	24	Noida	10000.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 example would display the following result.

UserID	UserName	Age	Location	Stipend
6	Ramesh	26	Hyderabad	23000.00
7	Pratyush	25	Delhi	20000.00

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

SELECT * FROM USERS

WHERE Location LIKE ‘N%u’;

This example would display the following result

UserID	UserName	Age	Location	Stipend
4	Neetu	22	Pune	15000.00

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

SELECT * FROM USERS

WHERE Location NOT LIKE ‘A%’;

This example would display all the users whose UserName does not begin with N.

UserID	UserName	Age	Location	Stipend
2	Neha	21	Kota	18000.00
3	Uday	24	Noida	10000.00
4	Neetu	22	Pune	15000.00
6	Ramesh	26	Hyderabad	23000.00
7	Pratyush	25	Delhi	20000.00

Recommended topics, DCL Commands in SQL and Tcl Commands in SQL

Frequently Asked Questions

What does the SELECT statement mean?
The SELECT statement in SQL is basically used to select data from a given 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 ;

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. It contains wildcard operators.

Key Takeaways

This blog teaches what wildcard operators are and how to use them with the LIKE clause. We then saw some examples of wildcard operators with the LIKE clause. I hope this blog clears the concept of wildcard operators for you 😊.

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