www.techonthenet.com Open in urlscan Pro
64.40.117.204  Public Scan

Form analysis
0 forms found in the DOM

Text Content

Advertisements


 1. Home
 2. Oracle / PLSQL
 3. Functions

JavaScript is required for this website to work properly. Please re-enable
JavaScript in your browser settings.
 * Databases
 * SQL
 * Oracle / PLSQL
 * SQL Server
 * MySQL
 * MariaDB
 * PostgreSQL
 * SQLite

 * MS Office
 * Excel
 * Access
 * Word

 * Web Development
 * HTML
 * CSS
 * JavaScript
 * Color Picker

 * Programming
 * C Language

 * More
 * ASCII
 * Unicode
 * Linux
 * UNIX
 * Techie Humor

Advertisements




ORACLE BASICS

 * ALIASES
 * AND
 * AND & OR
 * BETWEEN
 * COMPARISON OPERATORS
 * DELETE
 * DISTINCT
 * EXISTS
 * FROM
 * GROUP BY
 * HAVING
 * IN
 * INSERT
 * INSERT ALL
 * INTERSECT
 * IS NOT NULL
 * IS NULL
 * JOIN
 * LIKE
 * MINUS
 * NOT
 * OR
 * ORDER BY
 * PIVOT
 * REGEXP_LIKE
 * SELECT
 * SUBQUERY
 * TRUNCATE
 * UNION
 * UNION ALL
 * UPDATE
 * WHERE


ORACLE ADVANCED

 * Alter Table
 * Alter Tablespace
 * Change Password
 * Check Constraints
 * Comments in SQL
 * Create Schema
 * Create Schema Statement
 * Create Table
 * Create Table As
 * Create Tablespace
 * Create User
 * Data Types
 * Declare Variables
 * Drop Table
 * Drop Tablespace
 * Drop User
 * Error Messages
 * Find Default Tablespace
 * Find Users
 * Find Users Logged In
 * Find Version Information
 * Functions
 * Global Temporary
 * Grant/Revoke Privileges
 * Indexes
 * Literals
 * Local Temporary
 * Primary Keys
 * Procedures
 * Roles
 * Sequences
 * Set Default Tablespace
 * Synonyms
 * System Tables
 * Unique Constraints
 * Views


ORACLE CURSORS

 * Close Cursor
 * Cursor Attributes
 * Declare Cursor
 * Fetch Cursor
 * Open Cursor
 * Select For Update
 * Where Current Of


ORACLE EXCEPTION HANDLING

 * Named Programmer-Defined Exception
 * Named System Exception
 * WHEN OTHERS Clause
 * SQLCODE
 * SQLERRM


ORACLE FOREIGN KEYS

 * Disable Foreign Key
 * Drop Foreign Key
 * Enable Foreign Key
 * Foreign Key
 * Foreign Key (cascade delete)
 * Foreign Key (set null delete)


ORACLE LOOPS/CONDITIONALS

 * CURSOR FOR LOOP
 * EXIT
 * FOR LOOP
 * GOTO
 * IF-THEN-ELSE
 * LOOP
 * REPEAT UNTIL LOOP
 * WHILE LOOP


ORACLE TRANSACTIONS

 * Commit Transaction
 * Lock Table
 * Rollback Transaction
 * Set Transaction


ORACLE TRIGGERS

 * After Delete Trigger
 * After Insert Trigger
 * After Update Trigger
 * Before Delete Trigger
 * Before Insert Trigger
 * Before Update Trigger
 * Disable All Triggers
 * Disable Trigger
 * Drop Trigger
 * Enable All Triggers
 * Enable Trigger


STRING/CHAR FUNCTIONS

 * ASCII
 * ASCIISTR
 * CHR
 * COMPOSE
 * CONCAT
 * Concat with ||
 * CONVERT
 * DECOMPOSE
 * DUMP
 * INITCAP
 * INSTR
 * INSTR2
 * INSTR4
 * INSTRB
 * INSTRC
 * LENGTH
 * LENGTH2
 * LENGTH4
 * LENGTHB
 * LENGTHC
 * LOWER
 * LPAD
 * LTRIM
 * NCHR
 * REGEXP_INSTR
 * REGEXP_REPLACE
 * REGEXP_SUBSTR
 * REPLACE
 * RPAD
 * RTRIM
 * SOUNDEX
 * SUBSTR
 * TRANSLATE
 * TRIM
 * UPPER
 * VSIZE


NUMERIC/MATH FUNCTIONS

 * ABS
 * ACOS
 * ASIN
 * ATAN
 * ATAN2
 * AVG
 * BITAND
 * CEIL
 * COS
 * COSH
 * COUNT
 * EXP
 * FLOOR
 * GREATEST
 * LEAST
 * LN
 * LOG
 * MAX
 * MEDIAN
 * MIN
 * MOD
 * POWER
 * REGEXP_COUNT
 * REMAINDER
 * ROUND (numbers)
 * ROWNUM
 * SIGN
 * SIN
 * SINH
 * SQRT
 * SUM
 * TAN
 * TANH
 * TRUNC (numbers)


DATE/TIME FUNCTIONS

 * ADD_MONTHS
 * CURRENT_DATE
 * CURRENT_TIMESTAMP
 * DBTIMEZONE
 * EXTRACT
 * LAST_DAY
 * LOCALTIMESTAMP
 * MONTHS_BETWEEN
 * NEW_TIME
 * NEXT_DAY
 * ROUND (dates)
 * SESSIONTIMEZONE
 * SYSDATE
 * SYSTIMESTAMP
 * TRUNC (dates)
 * TZ_OFFSET


CONVERSION FUNCTIONS

 * BIN_TO_NUM
 * CAST
 * CHARTOROWID
 * FROM_TZ
 * HEXTORAW
 * NUMTODSINTERVAL
 * NUMTOYMINTERVAL
 * RAWTOHEX
 * TO_CHAR
 * TO_CLOB
 * TO_DATE
 * TO_DSINTERVAL
 * TO_LOB
 * TO_MULTI_BYTE
 * TO_NCLOB
 * TO_NUMBER
 * TO_SINGLE_BYTE
 * TO_TIMESTAMP
 * TO_TIMESTAMP_TZ
 * TO_YMINTERVAL


ANALYTIC FUNCTIONS

 * CORR
 * COVAR_POP
 * COVAR_SAMP
 * CUME_DIST
 * DENSE_RANK
 * FIRST_VALUE
 * LAG
 * LAST_VALUE
 * LEAD
 * LISTAGG
 * NTH_VALUE
 * RANK
 * STDDEV
 * VAR_POP
 * VAR_SAMP
 * VARIANCE


ADVANCED FUNCTIONS

 * BFILENAME
 * CARDINALITY
 * CASE
 * COALESCE
 * DECODE
 * EMPTY_BLOB
 * EMPTY_CLOB
 * GROUP_ID
 * LNNVL
 * NANVL
 * NULLIF
 * NVL
 * NVL2
 * SYS_CONTEXT
 * UID
 * USER
 * USERENV

--------------------------------------------------------------------------------


ORACLE / PLSQL: REGEXP_SUBSTR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL REGEXP_SUBSTR function
with syntax and examples.


DESCRIPTION

The Oracle/PLSQL REGEXP_SUBSTR function is an extension of the SUBSTR function.
This function, introduced in Oracle 10g, will allow you to extract a substring
from a string using regular expression pattern matching.


SYNTAX

The syntax for the REGEXP_SUBSTR function in Oracle is:

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )


PARAMETERS OR ARGUMENTS

string The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,
or NCLOB. pattern

The regular expression matching information. It can be a combination of the
following:

Value Description ^ Matches the beginning of a string. If used with a
match_parameter of 'm', it matches the start of a line anywhere within
expression. $ Matches the end of a string. If used with a match_parameter of
'm', it matches the end of a line anywhere within expression. * Matches zero or
more occurrences. + Matches one or more occurrences. ? Matches zero or one
occurrence. . Matches any character except NULL. | Used like an "OR" to specify
more than one alternative. [ ] Used to specify a matching list where you are
trying to match any one of the characters in the list. [^ ] Used to specify a
nonmatching list where you are trying to match any character except for the ones
in the list. ( ) Used to group expressions as a subexpression. {m} Matches m
times. {m,} Matches at least m times. {m,n} Matches at least m times, but no
more than n times. \n n is a number between 1 and 9. Matches the nth
subexpression found within ( ) before encountering \n. [..] Matches one
collation element that can be more than one character. [::] Matches character
classes. [==] Matches equivalence classes. \d Matches a digit character. \D
Matches a nondigit character. \w Matches a word character. \W Matches a nonword
character. \s Matches a whitespace character. \S matches a non-whitespace
character. \A Matches the beginning of a string or matches at the end of a
string before a newline character. \Z Matches at the end of a string. *? Matches
the preceding pattern zero or more occurrences. +? Matches the preceding pattern
one or more occurrences. ?? Matches the preceding pattern zero or one
occurrence. {n}? Matches the preceding pattern n times. {n,}? Matches the
preceding pattern at least n times. {n,m}? Matches the preceding pattern at
least n times, but not more than m times.

start_position Optional. It is the position in string where the search will
start. If omitted, it defaults to 1 which is the first position in the string.
nth_appearance Optional. It is the nth appearance of pattern in string. If
omitted, it defaults to 1 which is the first appearance of pattern in string.
match_parameter

Optional. It allows you to modify the matching behavior for the REGEXP_SUBSTR
function. It can be a combination of the following:

Value Description 'c' Perform case-sensitive matching. 'i' Perform
case-insensitive matching. 'n' Allows the period character (.) to match the
newline character. By default, the period is a wildcard. 'm' expression is
assumed to have multiple lines, where ^ is the start of a line and $ is the end
of a line, regardless of the position of those characters in expression. By
default, expression is assumed to be a single line. 'x' Whitespace characters
are ignored. By default, whitespace characters are matched like any other
character.

subexpression Optional. This is used when pattern has subexpressions and you
wish to indicate which subexpression in pattern is the target. It is an
integervalue from 0 to 9 indicating the subexpression to match on in pattern.


RETURNS

The REGEXP_SUBSTR function returns a string value.
If the REGEXP_SUBSTR function does not find any occurrence of pattern, it will
return NULL.


NOTE

 * If there are conflicting values provided for match_parameter, the
   REGEXP_SUBSTR function will use the last value.
 * If you omit the match_behavior parameter, the REGEXP_SUBSTR function will use
   the NLS_SORT parameter to determine if it should use a case-sensitive search,
   it will assume that string is a single line, and assume the period character
   to match any character (not the newline character).
 * See also the SUBSTR function.


APPLIES TO

The REGEXP_SUBSTR function can be used in the following versions of
Oracle/PLSQL:

 * Oracle 12c, Oracle 11g, Oracle 10g


EXAMPLE - MATCH ON WORDS

Let's start by extracting the first word from a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)')
FROM dual;

Result: 'TechOnTheNet '

This example will return 'TechOnTheNet ' because it will extract all
non-whitespace characters as specified by (\S*) and then the first whitespace
character as specified by (\s). The result will include both the first word as
well as the space after the word.

If you didn't want to include the space in the result, we could modify our
example as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)')
FROM dual;

Result: 'TechOnTheNet'

This example would return 'TechOnTheNet' with no space at the end.

If we wanted to find the second word in the string, we could modify our function
as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 2)
FROM dual;

Result: 'is '

This example would return 'is ' with a space at the end of the string.

If we wanted to find the third word in the string, we could modify our function
as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 3)
FROM dual;

Result: 'a '

This example would return 'a ' with a space at the end of the string.


EXAMPLE - MATCH ON DIGIT CHARACTERS

Let's look next at how we would use the REGEXP_SUBSTR function to match on a
single digit character pattern.

For example:

SELECT REGEXP_SUBSTR ('2, 5, and 10 are numbers in this example', '\d')
FROM dual;

Result: 2

This example will extract the first numeric digit from the string as specified
by \d. In this case, it will match on the number 2.

We could change our pattern to search for a two-digit number.

For example:

SELECT REGEXP_SUBSTR ('2, 5, and 10 are numbers in this example', '(\d)(\d)')
FROM dual;

Result: 10

This example will extract a number that has two digits side-by-side as specified
by (\d)(\d). In this case, it will skip over the 2 and 5 numeric values and
return 10.

Now, let's look how we would use the REGEXP_SUBSTR function with a table column
and search for a two digit number.

For example:

SELECT REGEXP_SUBSTR (address, '(\d)(\d)')
FROM contacts;

In this example, we are going to extract the first two-digit value from the
address field in the contacts table.


EXAMPLE - MATCH ON MORE THAN ONE ALTERNATIVE

The next example that we will look at involves using the | pattern. The |
pattern is used like an "OR" to specify more than one alternative.

For example:

SELECT REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u')
FROM dual;

Result: 'e'

This example will return 'e' because it is searching for the first vowel (a, e,
i, o, or u) in the string. Since we did not specify a match_parameter value, the
REGEXP_SUBSTR function will perform a case-sensitive search which means that the
'A' in 'Anderson' will not be matched.

We could modify our query as follows to perform a case-insensitive search as
follows:

SELECT REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'A'

Now because we have provide a match_parameter of 'i', the query will return 'A'
as the result. This time, the 'A' in 'Anderson' will be found as a match.

Now, let's quickly show how you would use this function with a column.

So let's say we have a contact table with the following data:

contact_id last_name 1000 Anderson 2000 Smith 3000 Johnson

Now, let's run the following query:

SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, 'a|e|i|o|u', 1, 1, 'i') AS "First Vowel"
FROM contacts;

These are the results that would be returned by the query:

contact_id last_name First vowel 1000 Anderson A 2000 Smith i 3000 Johnson o


EXAMPLE - MATCH ON NTH_OCCURRENCE

The next example that we will look at involves the nth_occurrence parameter. The
nth_occurrence parameter allows you to select which occurrence of the pattern
you wish to extract the substring for.


FIRST OCCURRENCE

Let's look at how to extract the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'e'

This example will return 'e' because it is extracting the first occurrence of a
vowel (a, e, i, o, or u) in the string.


SECOND OCCURRENCE

Next, we will extract for the second occurrence of a pattern in a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 2, 'i')
FROM dual;

Result: 'O'

This example will return 'O' because it is extracting the second occurrence of a
vowel (a, e, i, o, or u) in the string.


THIRD OCCURRENCE

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 3, 'i')
FROM dual;

Result: 'e'

This example will return 'e' because it is extracting the third occurrence of a
vowel (a, e, i, o, or u) in the string.

Share on:


Advertisements



Home | About Us | Contact Us | Testimonials | Donate



While using this site, you agree to have read and accepted our Terms of Service
and Privacy Policy.

Copyright © 2003-2022 TechOnTheNet.com. All rights reserved.