EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle WILDCARDS
 

Oracle WILDCARDS

Priya Pedamkar
Article byPriya Pedamkar

Oracle WILDCARDS

What is Oracle WILDCARDS?

Oracle WILDCARDS are referred for character pattern matching operation as a WILD CARD SEARCH. The WILDCARDS can be used with the LIKE operator. The WILDCARDS with LIKE operator allows us to perform character pattern matching.

 

 

There are two WILDCARDS in Oracle which are:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • %: It represents a sequence of any number of characters including zero.
  • _: It represents any single character but only at the specified position.
  • WILDCARDS with LIKE operator must be used in WHERE clause
  • WILDCARDS with LIKE operator can be used in SELECT statement
  • WILDCARDS with LIKE operator can be used for DML operations (INSERT, UPDATE, DELETE)

Syntax:

SELECT …….FROM Table_Name WHERE ColumnName LIKE WILDCARD             character/pattern/literal.

OR

SELECT col_1, col_2…..col_n FROM Table_Name WHERE ColumnName LIKE           ‘pat%’;

Description:

  • Col_1/2/n: The column(s) or calculation as per your requirement.
  • Table_Name: As per your requirement
  • WHERE: It’s mandatory to use this operator.
  • Column_Name: The condition will be applied to the column to filter the data.
  • LIKE: It’s an operator that allows WILDCARDS to be used.

Implementations of WILDCARDS with Examples

In this section, we’ll see the implementation of Oracle WILDCARDS and its behavior. For that, we will use the below sample table (Employee & Dept_category) with 14 & 8 records to understand the Oracle WILDCARDS behavior.

Code:

SELECT * FROM Employee;

Output:

Oracle WILDCARDS-1.1

Code:

SELECT * FROM Dept_category;

Output:

Oracle WILDCARDS-1.2

1. % WILDCARD

Code:

SELECT Name, Designation, DOJ, Salary FROM Employee WHERE Name LIKE 'CH%';

Output:

Oracle WILDCARDS-1.3

In the above SELECT statement % WILDCARD with LIKE operator filter the record(s) based on the condition. It returns only those records which name begins with character pattern “CH” in the Name column and returns the matched record(s). The SQL statement fetched ‘CHANDAN’ details because in the Name column only CHANDAN starts with the “CH” character. % represents any sequence of any number of characters including zero.

2. % WILDCARD with NOT keyword

Code:

SELECT Name, Designation, DOJ, Salary FROM Employee WHERE Name NOT LIKE 'B%';

Output:

Oracle WILDCARDS-1.4

In this output, two records are missing, WHY?

Because the above SELECT statement contains NOT keyword with WILDCARD and LIKE operator. It means this SQL statement selects only those records which does not start with the supplied pattern ‘B’ with % WILCARD.

Code:

SELECT Name, Designation, DOJ, Salary FROM Employee WHERE Name LIKE '%E%';

Output:

Oracle WILDCARDS-1.5

In this example, % WILDCARD is being used twice after and before “E”. It means it selects the entries from Name column which consists of ‘E’ character at least once at any position and displays the records.

3. Underscore ( ‘_’) WILDCARD

Code:

SELECT Name, Designation, DOJ, Salary FROM Employee WHERE Designation LIKE '_O%'

Output:

Oracle WILDCARDS-1.6

As underscore (‘_’) WILDCARD represents any single character at the specified position.

So this statement searches the entry in the Designation column which is having the second character ‘O’, but the first character can be anything. And returns the matching pattern as a result.

4. Underscore ( ‘_’) WILDCARD with NOT keyword

Code:

SELECT Name, Designation, DOJ, Salary FROM Employee WHERE Designation NOT LIKE '_O%';

Output:

Output-1.7

So this statement having NOT keyword with WILCARD, which means it searches the entry in the Designation column which does not consist the second character ‘O’, but the first character can be anything. And returns the matching pattern as a result.

5. Drawback of WILDCARDS

Example #1

SELECT Department FROM Dept_Category WHERE Department LIKE '%_%';

Output:

Output-1.8

The above SELECT statement returns all rows from the Dept_Category table but the expected result was the record(s) which consists only ‘_’ character because of the WILDCARD condition. So it’s the wrong output.

Example #2

SELECT deptno, dname, loc FROM Dept WHERE dname LIKE ‘%%%’;

Output:

Output-1.9

The above SELECT statement returns all rows from the Dept_Category table but the expected result was the record(s) which consists only ‘%’ character because of the WILDCARD condition. So it’s the wrong output.

There are two questions arise:

Why this issue occurred when we used correct syntax in the above two examples?

  • In the above two examples, Oracle Optimizer considers _ & % as a WILDCARD, not a character.

How to fix this problem?

  • To fix this issue/drawback Oracle provides ESCAPE Clause.

ESCAPE Clause: ESCAPE clause is used to find the exact match for ‘_’ & ‘%’ as a character. ESCAPE clause has to be used along with ‘\’.

6. Underscore (‘_’) WILDCARD with ESCAPE Clause

Code:

SELECT Department FROM Dept_Category WHERE Department LIKE '%\_%' ESCAPE ‘\’;

Output:

Output-1.10

In the above example, Oracle optimizer considers ‘_’ as a character, not as a WILDCARD because of the ESCAPE clause. And it returns the expected result.

7. Underscore (‘%’) WILDCARD with ESCAPE Clause

Code:

SELECT Department FROM Dept_Category WHERE Department LIKE '%\%%' ESCAPE ‘\’;

Output:

Output-1.11

In the above example, Oracle optimizer considers ‘%’ (second from left) as a character, not as a WILDCARD because of the ESCAPE clause. And it returns the expected result.

Note: The keywords @, $, &, ^, ? Etc. can be used instead of ‘\’ with escape option to pass the WILDCARDS symbol as a character in a SQL query to get the exact match.

  • For Case sensitive character search, character function (UPPER, LOWER or INITCAP) can be used with WILDCARDS

Code:

SELECT Department, State FROM Dept_Category WHERE Department LIKE UPPER ('%^%ra%') ESCAPE '^';

Output:

Oracle WILDCARDS-1.12

  • Logical operator (AND, OR) can be used for multiple WILDCARD conditions

Code:

SELECT Department, State FROM Dept_Category WHERE LOWER (Department) LIKE ('%^%ra%') ESCAPE '^' OR  LOWER (Department) LIKE ('%!_de%') ESCAPE '!';

Output:

Oracle WILDCARDS-1.13

Note: In this case, query performance can be poor if the Table is big.

Oracle WILDCARDS can be applied to any data type.

Conclusion

Oracle WILDCARDS are keyword or symbol which enables us for character pattern matching operation on any data type column. Oracle WILDCARDS is a good option to find the record(s) in the scenario where we don’t have the exact content of the column.

Recommended Articles

This is a guide to Oracle WILDCARDS. Here we discuss the Introduction and the different wildcards in oracle along with different examples and its code implementation. You may also have a look at the following articles to learn more –

  1. Introduction to Oracle LIKE Operator
  2. Features & Types of Wildcards in MySQL
  3. How does Full Outer Join Work?
  4. BETWEEN in Oracle
  5. Guide to Oracle Window Functions
  6. Oracle While Loop | How to Works?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW