Regular Expression in Oracle SQL

Regular Expression in Oracle SQL is a pattern matching technique to retrieve sort of data based on the conditions given in Oracle database.

Pattern Matching is similar to functions like, replace, substring and in string which are previously seen topics.

At the end of this article there will be certain task to practice for this Regular Expressions. It is very important to be very clear about this topic, this are very helpful for many interview Questions.

Using regular expressions with Oracle Database: Overview

Regular Expression is one of the easiest way to learn with clear understanding if one are familiar with the below

• Basic string matching patterns,
• Quantifiers,
• Meta characters,
• Functions etc.

Note: The above points should understand clearly means it will be very easy to write a query.

Basic String Matching with Regular Expressions

|	Either or
()	Group
\	Escape
^	Start
$	Stop

Literals of regular expression in oracle sql

[ a b c ]	a, b, c
[^ a b c]	Any character except a, b, c
[ a - z ]	a to z all characters
[ A - Z ]	A to Z all characters
[ a- z A-Z]	All letters small or caps
[ 0 - 9]	Numbers between 0 to 9

Quantifiers used in Regular Expression of SQL

[ ] ?	Occurs 0(zero) or 1 times
[ ] +	Occurs 1 or more times
[ ] *	Occurs 0 or more times
[ ] {n}	Occurs n times
[ ] {n, }	Occurs n or more times
[ ] {y, z}	Occurs at least y times but less than z times

Meta characters in Regular Expression of SQL 

\d	[0-9]
\D	[^0-9]
\w	[a-z A-Z 0-9]
\W	[^\w ]

Oracle Database SQL Functions for Regular Expressions

In Oracle Database SQL functions consists of 5 functions in regular expressions such as:-

1. REGEXP_LIKE
2. REGEXP_SUBSTR
3. REGEXP_INSTR
4. REGEXP_REPLACE
5. REGEXP_COUNT

This are the five most important regex pattern matching expressions are going to see detail with examples and explanations 

REGEXP_LIKE

Like operator is used to get the record for the giving pattern.

This Regexp_like can be used in Where condition not in SELECT clause.

Let's Understand some basic match parameters which are helps to get the record based on our input string.

‘i’	Case insensitive Matching
‘c’	Case sensitive Matching
‘n’	Allows the period  (.), to match new line character
‘m’	The source string will be in multiple line

End of the Studying Part.

If you completed reading the above tables or charts, is only enough to retrieve the query 

From the above information, now we are going to workout all the patterns with examples

We will use this table for data retrieving.

Table Name : detail

Script for the above table

----- Creating a table detail CREATE TABLE detail        (id NUMBER        ,Name VARCHAR2(20)        ,email VARCHAR2(50)        ,Amount NUMBER        ,Contact VARCHAR2(15)        );

------ Inserting a record in a table      BEGIN        INSERT INTO detail VALUES (1000, 'Kaviarasan', 'kavi.777@gmail.com', 50000, '+91 90000 0000');        INSERT INTO detail VALUES (2300, 'karthi', 'karthi.111@gmail.com', 90000, '+1 90008 0000');        INSERT INTO detail VALUES (8900, 'mohan', 'mohan4444@yahoo.com', 55000, '+10 90000 0000');        INSERT INTO detail VALUES (1070, 'Bill', 'bill.kevin@gmail.com', 500000, '+044 23000 0000');        INSERT INTO detail VALUES (0300, 'Rahul', '7777rahul@gmail.com', 200000, '+28 90500 0000');        INSERT INTO detail VALUES (0020, 'rekha', 'rek18son@gmail.com', 40000, '+91 89000 0000');      COMMIT; END; /

REGEXP_LIKE patterns with examples

Basic Syntax : REGEXP_LIKE (source string, pattern)

1, Print the Name which starts with letter 'k' it may be any cases (Upper or lower)

^  which is used to denote starting with letter (k)

The above query only print Lower case "k" but not prints Upper case "K".

Similarly instead of Lower "k" if we give "K" means it will print upper case name only.

To print all (K and k) we need to use (i) which mentions case in sensitive matching.

2) Print the name which contains "a" 

From the above query the records which are fetched are single "a" Name.

{1} it is a quantifiers used to mention the n times.

WHERE REGEXP_LIKE (NAME, '[a]{2}') => it is used to print two continuous "a"

 Example: aarthy, raam, aakash, saami, etc..

Same as for three, four etc...

3) Print the name that must contain the letter (r, s, t, u, v, w, x, y, z)

[r-z] patterns used to print the letter from r to z of all characters (literals in regular expression).

4) Print the name that must contain all character such as "akr"

 and write another query to print any of the name should contain this any one character "akr"

5) Print the name that should starts with "m" and ends with "n"

SELECT name FROM detail

WHERE REGEXP_LIKE (name, '^m[a-z]+n$');

^m  used to define letter starts with m

[a-z] next letter will be between a to z

+ denotes 1 or more times

n 

$ means ends

6) The name that should ends with vowels (a, e, i, o, u)

SELECT * FROM detail

WHERE REGEXP_LIKE (Name, '[a-z]+(a|e|i|o|u)$');

() -  group

| - used to identify either or 

$ - used to end the word

7) Find the Gmail user and yahoo user in the table

It's easy to get the users by using regular expression, by giving directly in the pattern.

8) Print the ID and NAME that contains first letter should be between (a) to (z)

CONCLUSION

I hope you got some clear understanding about the basic patterns to regular expression in oracle SQL, and as well as regexp_like functions.

This is the first part of the regular expression functions and other 4 functions are explained in next 2 parts.

If anyone having any queries or doubts means kindly reach out us through official mail or comment below.