Home » Oracle » Oracle Sql » Regexp in oracle : functions ,Regular expression

Regexp in oracle : functions ,Regular expression

This is part of the Oracle SQL tutorial and has good examples and explanations on Regexp in Oracle.

regexp in oracle

  • String manipulation and searching contribute to a large percentage of the logic within a Web-based 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”).
  • From 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 where the match is found
REGEXP_SUBSTR: Searches for a regular expression pattern within a given string and
returns the matched sub-string

SELECT first_name, last_name, salary,
TO_CHAR(hire_date, 'yyyy')
FROM emp
WHERE REGEXP_LIKE(
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.

Integrity Constraints using regexp in oracle

Regular expressions are a useful way to enforce integrity constraints. For example, suppose that you want to ensure that phone numbers are entered into the database in a standard format. Let’s create a emp_contact table and add a check constraint to the phone_number column to enforce the following format mask
(AA) AAA-AAA-AAA

CREATE TABLE emp_contact
(
emp_name    VARCHAR2(30),
phone_number  VARCHAR2(30)
CONSTRAINT phone_number_format
CHECK ( REGEXP_LIKE ( p_number, '^(\d{2}) \d{3}-\d{3}-\d{3}$' ) )
);
regexp in oracle

Regular expression meta character in Oracle Database

regexp in oracle

Hope you like this post on regexp in oracle

See also  Opmnctl command every administrator should know

Related Articles
Oracle PLSQL Tables
Date functions in Oracle
Oracle decode
Oracle Case Statement
Group by Oracle
https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_regexp.htm

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top