SQL Wildcard Operators

PRASHANT SINGH
Last Updated: May 13, 2022

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

 

Frequently Asked Questions

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

Also, try CodeStudio to practice programming problems for your complete interview preparation. Happy Learning. 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 ?
1 upvote

Comments

No comments yet

Be the first to share what you think