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 REGEXP
 

Oracle REGEXP

Updated February 28, 2023

Oracle REGEXP

 

 

Introduction to Oracle REGEXP

The following article provides an outline for Oracle REGEXP. Oracle Regular Expression is a technique which is used to represent a group of strings according to a particular format / pattern. Regular Expression is a declarative mechanism to represent a group of string. Regular Expression allows to specify complex patterns of character sequence.

Watch our Demo Courses and Videos

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

Points of Concentration:

  • All four functions can be used on any data type that consists character data. These are CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2.
  • For better readability of the code, Regular expression must be enclosed between single quote.
  • Oracle provides four sets of SQL functions for using regular expressions that allow us to manipulate or search strings.

Syntax of SQL Functions:

Oracle provides four SQL functions that are being used for Regular Expressions.

REGEXP_LIKE (Expression, Pattern, Match_Pattern)

REGEXP_REPLACE(string, pattern[, replacement_string[, start_position[, nth_appearance [, Match_Pattern]]]])

REGEXP_SUBSTR(string, pattern[, start_position[, nth_appearance[, match_parameter[, sub_expression]]]])

REGEXP_INSTR(string, pattern[, start_position[, nth_appearance[, return_option[, match_parameter[, sub_expression]]]]])

Description:

  • Expression: It can user provided expression or column name.
  • Pattern: It is a searching pattern.
  • Replacement_String: It’s optional. It replaces match pattern. If this parameter is omitted, function will remove the match pattern.
  • start_position: It’s optional. Search begins at given position. If it’s omitted, starts from the first position. By default value 1.
  • nth_Appearance: It’s optional. It indicates number of occurrence of given pattern. If omitted, default value is 1.

Operators used for Oracle Regular Expression

There are various types of operators available:

  • Pre-Defined Character Classes
  • Quantifiers Operators
  • Character Classes
  • Others

1. Pre-Defined Character Classes

\s Space Character.
\S Any Character Except Space Character.
\d Any Digit From 0 to 9.
\D Any Character Except Digit.
\w Any Word Character [a-z A-Z 0-9].
\W Any Character Except Word Character (Special Character).
. Any Character Including Special Character.
\A Matches at the Beginning or at the ending of a string.
\Z Matches at the End of a String.

2. Quantifier Operators

Quantifier operators are used to quantify the number(s) of character.

a Exactly one a.
a+ At least one a.
a* Any number of a’s including ZERO number.
a? At most ONE a i.e. either ZERO number or ONE number.
a{m} Exactly m number of a’s.
a{m, n} Minimum m number of a’s and Maximum n number of a’s.
Note: Here ‘a’ is a character for example, ‘m’ & ‘n’ can be any number.

3. Character Classes

Character class operators can be used to search a group of characters.

[abc] Either a or b or c.
[^abc] Except a, b and c.
[a – z] Any lower case alphabet.
[A – Z] Any upper case alphabet.
[a – z A – Z] Any alphabet.
[0 – 9] Any digit from 0 to 9.
[a – z A – Z 0 – 9] Any alpha numeric character.
[^a – z A – Z 0 – 9] Except alpha numeric character (Special character).

4. Others

^a It will check whether target string starts with a or not.
a$ It will check whether target string ends with a or not.
| Nature is ‘OR’, allows to specify more than one alternative
( ) Used to group expressions as a subexpression
‘c’ Perform case sensitive.
‘i’ Perform case insensitive.
‘n’ Allows period character to match new line character.
‘m’ Indicates to have multiple lines.
‘x’ Indicates to ignore whitespace character.

Examples of Oracle REGEXP

Here we will use the below sample table (Employee) with 14 records for the Oracle Regular Expression behavior.

Code:

SELECT * Employee;

Output:

Oracle REGEXP op 1

Example #1

REGEXP_LIKE (Expression, Pattern, Match_Pattern)

This Regular Expression can be applied in WHERE clause to perform string matching.

It can be applied in SELECT, INSERT, UPDATE, or DELETE statement.

It is very similar to Oracle LIKE operator, LIKE operator performs for simple search and REGEXP_LIKE function is used for complex search.

Code:

SELECT Name FROM Employee WHERE REGEXP_LIKE (Name, '^A(*)');

Output:

Oracle REGEXP op 2

The above statement returns two rows out of 14 because in the Regular expression condition ^A checks the name which starts with ‘A’ and (*) check any number of ‘A’ occurrences. And it finds two rows.

Example #2

REGEXP_REPLACE (string,pattern[,replacement_string[,start_position[,nth_appearance [,Match_Pattern]]]])

This Regular Expression helps us to replace complex matching string.

Code:

SELECT REGEXP_REPLACE ('EDUCBA is a great platform to learn', '^(\S*)','
https: //www.educba.com/') EDUCBA_Link FROM dual;

Output:

first word should be character

In the above output ‘EDUCBA’ replaced with’https: //www.educba.com/’ because condition ^ (\S*) checks the first word should be character with any number of occurrences.

Example #3

REGEXP_ SUBSTR (string, pattern[, start_position[, nth_appearance[, match_parameter[, sub_expression]]]])

  • The REGEXP_ SUBSTR () function is an extension of Oracle SUBSTR function.
  • It allows to extract a substring using Regular Expression Operators.
  • sub_expression: It’s an optional. If pattern has sub expression then it can be used to target the pattern sub expression.
  • It returns string value.
  • Returns NULL if pattern does not find.

Code:

SELECT REGEXP_SUBSTR ('EDUCBA is a great learning platform', '(\S*)(\s)') Substring FROM DUAL;

Output:

it specifies to extract all non-space characters

The above example returns ‘EDUCBA’ because (\S*) it specifies to extract all non-space characters and (\s) it specifies to extract first space characters. So result includes first character as well as first space after the word.

Example #4

REGEXP_INSTR  (string, pattern[, start_position[, nth_appearance[, return_option[, match_parameter[, sub_expression]]]]])

  • The REGEXP_ INSTR () function is an extension of Oracle INSTR function.
  • It returns ZERO ‘0’ if pattern does not find.
  • It returns string value.
  • sub_expression: It’s an optional. If pattern has sub expression then it can be used to target the pattern sub expression.

Code:

SELECT REGEXP_INSTR('EDUCBA is a great learning platform',’t’, 1, 1, 0, 'i')FROM DUAL;

Output:

num

The above example returns numeric value ‘17’ because ‘t’ exist at 17th position because of the parameters start_position is 1, an nth_appearance is 1, a return_option is 0, and a match_parameter of ‘i’. Here ‘i’ checks for upper or lower case of t in short it indicates to ignore case based search.

Conclusion

Oracle Regular Expression is a standardized syntax convention technique that allows us to pattern search for in string data. Oracle Regular Expressions are a very powerful text processing functions. Oracle Regular Expression is very useful while searching for complex pattern string.

Recommended Articles

This is a guide to Oracle REGEXP. Here we discuss the introduction to Oracle REGEXP with operators used for regular expression and examples. You may also have a look at the following articles to learn more –

  1. FETCH in Oracle
  2. Oracle UNIQUE Constraint
  3. Oracle WILDCARDS
  4. Natural Join in Oracle

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