REGEXP string Manipulation

Last updated on July 17th, 2015 at 06:05 pm

String manipulation and searching contribute to a large percentage of the logic within a Webbased
application. Regular expressions are a method of describing both simple and complex
patterns for searching and manipulating. Usage ranges from the simple (“Find the word SAN in
the text”) to the more complex (“Extract all URLs from the text”) to the archaic (“Find all words
in which every second character is a vowel”).
In Oracle Database 10g, you can use both SQL and PL/SQL to implement regular expression
support. The National Language Support Run Time Library (NLSRTL) provides all of the
linguistic elements used to implement regular expression support. The following functions have
been introduced:
• REGEXP_LIKE: Similar to the LIKE operator, but performs regular expression matching
instead of simple pattern matching
• REGEXP_REPLACE: Searches for a regular expression pattern and replaces it with a
replacement string
• REGEXP_INSTR: Searches for a given string for a regular expression pattern and returns
the position were the match is found
• REGEXP_SUBSTR: Searches for a regular expression pattern within a given string and
returns the matched substring

SELECT first_name, last_name, salary,
TO_CHAR(hire_date, ‘yyyy’)
FROM emp
TO_CHAR(hire_date, ‘yyyy’), ‘^201[1-4]$’);

This example illustrates the use of the REGEXP_LIKE functions to display employee names and
salaries for employees hired between 2011 and 2014. The example uses the ‘^’ to indicate that
the beginning of the line has to be 201, and [ – ] with ‘$’ to specify a range of valid characters as
the very last character.

Leave a Reply