Regular Expression Support

  1. Regular Expression Overview

Regular Expressions enable you to search for patterns in string data by using standardized syntax conventions.

String manipulation and searching contribute to a large percentage of logic within Web-Based applications.
Using the Regular Expressions we can even find out the words whose every second character is a vowel.

  1. Meta Characters

These characters are special characters that have a special meaning, such as a wild character a repeating character a nonmatching character, or a range of characters.

Symbol Description

  • Matches zero or more occurances
    | Alteration Operator to specify alternative match
    ^/$ Matches the start-of-line/end-of-line
    [] Bracket expression for a matching list matching any
    one of the expressions represented in the list.
    Match anything inside the square brackets for ONE character position and only once.
    For ex. [12] means match the target to 1 and if that does not match then match the target to 2 while [0123456789] means match to any character in the range 0 to 9.
    {m} Matches exactly m times
    {m,n} Matches at least m times but no more than n times
  1. Using Meta Characters

With the following explanations given below, we can understand the significance of Meta Characters while searching for a specific string.
a. Find ‘abc’ within a String

End Result should be ‘abc’ alone and not def,dfe,acd etc.

b. Find ‘a’ followed by any character and followed by ‘c’
Meta Character to be used : ‘.’
End Result should be ‘a.c’,’abc’,’adc’,’a&c’ alone and not ‘abb’,’a&b’

c. Find one or more occurances of ‘a’ from a String.
Meta Character to be used : ‘+’ to match one or more of the previous characters.
End result should be ‘a+’,’a’,’aa’ alone and not ‘bb’,’b’

  1. Regular Expressions Functions

a. REGEXP_LIKE – Very similar to the LIKE Operator but performs regular expression matching instead of simple pattern matching.

b. REGEXP_REPLACE – Searches for a regular expression pattern and replaces it with a replacement string.

c. REGEXP_INSTR – Searches for a given string for a regular expression pattern and returns the position where the match is found.

d. REGEXP_SUBSTR – Searches for a regular expression pattern within a given string and returns the matched substring.

A Regular Expression must be enclosed within single quotation marks. Doing so ensures that the entire expression is interpreted by the SQL Function and can improve the readability of the code.

  1. REGEXP Function Syntax

REGEXP_LIKE (srcstr,pattern [,match_option])

srcstr – Search Value
pattern – Regular Expression
match_option – Option to change default matching. It can include one or more of the following values:
‘c’ uses case-sensitive matching
‘i’ uses non-case-sensitive matching
‘n’ allows match-any-character-operator
‘m’ treats source string as multiple line.

REGEXP_INSTR (srcstr,pattern [,position [, occurence [, return_option [, match_option]]]])

srcstr – Search Value
pattern – Regular Expression
position – Search starting position
occurence – Occurence to search for
return_option – Start or End Position of occurence
match_option – Option to change default matching. It can include one or more of the following values:
‘c’ uses case-sensitive matching
‘i’ uses non-case-sensitive matching
‘n’ allows match-any-character-operator
‘m’ treats the source string as multiple lines.

REGEXP_SUBSTR (srcstr, pattern [, position [, occurence [, match_option ]]])
srcstr – Search Value
pattern – Regular Expression
position – Search starting position
occurence – Occurence to search for
match_option – Option to change default matching. It can include one or more of the following values:
‘c’ uses case-sensitive matching
‘i’ uses non-case-sensitive matching
‘n’ allows match-any-character-operator
‘m’ treats source string as multiple line.

REGEXP_REPLACE (srcstr, pattern [, replacestr [, position [, occurence [, match_option]]]])
srcstr – Search Value
pattern – Regular Expression
replacestr – Character String Replacing Pattern
position – Search starting position
occurence – Occurence to search for
match_option – Option to change default matching. It can include one or more of the following values:
‘c’ uses case-sensitive matching
‘i’ uses non-case-sensitive matching
‘n’ allows match-any-character-operator
‘m’ treats source string as multiple line.

  1. Performing Basic Searches

Ex.

— SQL Query to display the Names having either Steven or Stephen using REGEXP_LIKE Function —

CREATE TABLE tab_regexp_like
(name VARCHAR2(50))
/

INSERT INTO tab_regexp_like VALUES(‘Steven’)
/

INSERT INTO tab_regexp_like VALUES(‘Stephen’)
/

SELECT * FROM tab_regexp_like
/

SELECT name FROM tab_regexp_like
WHERE REGEXP_LIKE(name,’^Ste(v|ph)en$’)
/

— Displays only Stephen and not Steven —
SELECT name FROM tab_regexp_like
WHERE REGEXP_LIKE(NAME,’^Ste(ph)en$’)
/

— Displays only Steven and not Stephen —
SELECT name FROM tab_regexp_like
WHERE REGEXP_LIKE(name,’^Ste(v)en$’)
/

— Displays both Steven and Stephen —
SELECT name FROM tab_regexp_like
WHERE REGEXP_LIKE(name,’^Ste(v|ph)en$’)
/

In the query all names having Steven or Stephen are displayed.
‘^Ste(v|ph)en$’
a.^ – Indicates the beginning of the sentence
b.$ – Indicates the end of the sentence
c.| – Indicates either/or

DROP TABLE tab_regexp_like PURGE
/

CREATE TABLE phone_tab(phone_number VARCHAR2(150))
/

INSERT INTO phone_tab VALUES(‘112.113.114.456789’)
/

INSERT INTO phone_tab VALUES(‘113.114.115.45422’)
/

INSERT INTO phone_tab VALUES(‘114.115.116.4567’)
/

INSERT INTO phone_tab VALUES(‘115.116.117.4567’)
/

  1. Checking the Presence of a Pattern

Ex.

— SQL Query to search the street address to find the location of the first nonalphabetical character,regardless of whether it is in uppercase or lower case.

CREATE TABLE tab_regexp_instr
(name VARCHAR2(50))
/

INSERT INTO tab_regexp_instr VALUES(‘100Mumbai’)
/

INSERT INTO tab_regexp_instr VALUES(‘Mumbai12Lohar’)
/

INSERT INTO tab_regexp_instr VALUES(‘MUMBAI12Lohar’)
/

SELECT name,regexp_instr(name,'[^[:alpha:]]’) “Search”
FROM tab_regexp_instr
/

In the example, REGEXP_INSTR Function is used to search the street address to find the location of the first nonalphabetical character, regardless of whether it is in an upper-class or lower-class.
Note that [::] implies a character class and matches any character from within that class and [:alpha:] matches with any alphabetic character.

The expression [[:upper:]]+ searches for one or more consecutive uppercase characters.
This expression matches DEF in the string abcDEFghi but does not match the string abcdefghi.

  1. Example of Extracting Substrings

— Example which searches for ‘,’ followed by more occurrences of non-comma characters followed by a comma —
SELECT
REGEXP_SUBSTR(‘500 Oracle Parkway, Redwood Shores, CA’,
‘,[^,]+,’) “REGEXPR_SUBSTR”
FROM dual
/

REGEXPR_SUBSTR

, Redwood Shores,

In the expression ‘[^ ]+ ‘ indicates
1.[ = starts the expression
2.^ = indicates not in
3.] = ends the expression
4.+ = indicates 1 or more

  1. Replacing Patterns

— SQL Query to Reformat the pattern with a Space after each NON-NULL Character in a String —

SELECT REGEXP_REPLACE(ENAME,'(.)’,’\1 ‘) “Replace”
FROM EMP
/

  1. Regular Expressions and Check Constraints

Regular Expressions can also be used in Check Constraints.

CREATE TABLE EMP8(ID NUMBER,EMAIL VARCHAR2(50) CHECK(REGEXP_LIKE(EMAIL,’@’)))
/

INSERT INTO EMP8 VALUES(100,’pushpjeet@gmail.com’)
/

INSERT INTO EMP8 VALUES(100,’pushpjeetgmail.com’)
/

drop table EMP8 purge
/

The following function call uses the x flag to match the first string by ignoring spaces in the regular expression:

SELECT REGEXP_SUBSTR(‘oracle’, ‘o r a c l e’, 1, 1, ‘x’) FROM DUAL
/

© 2024 AWS. Learn from Scratch