EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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:

ADVERTISEMENT
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • %: 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?
ADVERTISEMENT
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
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
  • Blog as Guest
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

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW