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:
- %: 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:
Code:
SELECT * FROM Dept_category;
Output:
1. % WILDCARD
Code:
SELECT Name, Designation, DOJ, Salary FROM Employee WHERE Name LIKE 'CH%';
Output:
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:
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:
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:
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:
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:
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:
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:
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:
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:
- 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 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 –
