• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » Regexp in oracle : functions ,Regular expression

Regexp in oracle : functions ,Regular expression

September 16, 2022 by techgoeasy Leave a Comment

This is part of Oracle SQL tutorial and has good examples, explanation 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 were 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. Lets creates a emp_contact table and adds 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

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

Filed Under: Oracle, Oracle Sql Tagged With: regexp

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us