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


Comments and Discussions!

Load comments ↻





Copyright © 2024 www.includehelp.com. All rights reserved.