# 22. Virtuoso Functions Guide - regexp_substr

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
Replication
SOAP
SQL
String
ascii
atoi
blob_to_string
blob_to_string_outpu...
chr
initcap
isblob
isstring
lcase
left
length
locate
ltrim
make_string
regexp_instr
regexp_like
regexp_match
regexp_parse
regexp_replace
regexp_substr
repeat
replace
right
rtrim
search_excerpt
serialize
space
split_and_decode
sprintf
strcasestr
strchr
string_output
string_output_flush
string_output_gz_com...
string_output_string
string_to_file
strrchr
strstr
subseq
substring
tmp_file_name
trim
ucase
upper
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY
Functions Index

## regexp_substr

returns a single captured substring from matched substring
(varchar|nvarchar) regexp_substr (in pattern (varchar|nvarchar), in str (varchar|nvarchar), in offset integer);
Parameters
pattern – the regexp pattern string
str – the data into which 1 (the first) or 0 matching substrings will be searched for
offset – the number of regexp subexpression who's match to return : 0 for the whole matching substring, 1 for the substring matching the first regexp subexpression and so on
Description

This function will return the whole string value of the first substring in "str" that matches the regexp in "pattern" or a sub part of the first match. The regexp syntax allows subexpressions to be marked in the regular expression (using the braces syntax). An example of such type of expression will be: '(2[34]).*(2[35])' which means a regular expression having two subexpressions: '2[34]' and '2[35]'.

Let's apply the above regexp to the following source string: 22232225222323

    SQL> select regexp_substr('(2[34]).*(2[35])','22242226222527', 0);
    callret
    VARCHAR
    _______________________________________________________________________________

    2422262225

    1 Rows. -- 0 msec.

This returns the hole matched string from the expression.

    SQL> select regexp_substr('(2[34]).*(2[35])','22242226222527', 1);
    callret
    VARCHAR
    _______________________________________________________________________________

    24

    1 Rows. -- 0 msec.

This returns what has been matched for the first ('2[34]') regexp subexpression out of the whole matched substring (see above) : basically denoted as \1 in perl

    SQL> select regexp_substr('(2[34]).*(2[35])','22242226222527', 2);
    callret
    VARCHAR
    _______________________________________________________________________________

    25

    1 Rows. -- 10 msec.

This returns what has been matched for the second ('2[35]') regexp subexpression out of the whole matched substring. This is \2 in perl. Note that the text '22' (that corresponds to '.*' part of the regexp) is not returned by the above call because it is not contained in the second pair of braces.

See Also

regexp_match()

regexp_like()

regexp_parse()

regexp_replace()

regexp_instr()