Home » SQL

Wildcard Operators in SQL

In this article, we are going to learn Wildcard operators in SQL (Structure Query Language). Learn how to use wildcard operator in SQL Query?
Submitted by Preeti Jain, on March 03, 2018

Wildcard operators are used in conjunction with like operator to enhance the search in a table.

Wildcard characters (% or _) can be used anywhere (left , right, in between) in a word.

We may use multiple wildcard characters in a single word depend on our requirements.

1. % (Percentage or percent sign)
2. _ (Underscore)

1) % (Percentage or percent sign)

This wildcard characters represents zero, one or more character (i.e. this is available for matching a string for any number of characters (0, 1, 2, ... and so on)).

Syntax:

SELECT column_name(s) from table_name like '%ppp%';

Here, "ppp" is any combination of a word or string.

Database table:

Eid	Ename	Salary
101	preeti	20000
102	apoorv	40000
103	antima	50000
104	rahul	10000
105	pankaj	5000

Example 1: Write a query to find all employees whose name starts with 'p'

SELECT ename from employee where ename like 'p%';

Output:

    ename
    preeti
    pankaj

Example 2: Write a query to find all employees ids whose name ends with 'i'

SELECT eid from employee where ename like '%i';

Output:

    Eid
    101

2) _ (Underscore)

This wildcard characters represents only one character (i.e. this is available for matching a string for a single character only (i.e. not compare with zero or more than one character)).

Syntax:

SELECT column_name(s) from table_name like '_uuu_';

Here, "uuu" is any combination of a word or string.

Example 1: Write a query to find all employees whose name of second letter starts with 'p'

SELECT ename from employee where ename like '_p%';

Output:

    ename
    apoorv

Example 2: Write a query to find all employees ids whose name’s last second letter ends with 'i'

SELECT eid from employee where ename like '%u_';

Output:

    ename
    apoorv