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
RDF data
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
sprintf_inverse
sprintf_iri
sprintf_iri_or_null
sprintf_or_null
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_instr

Returns the first position of the occurrence of a regular-expression pattern in a given string.
integer regexp_instr (in source_string varchar, in pattern varchar, [in start_position integer], [in occurrence integer], [in return_option integer], [in match_parameter integer]);
Description

This function looks for a pattern and returns the first position of the pattern. Optionally, you can indicate the start_position you want to begin the search. The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence. The default value of the return_option is 0, which returns the starting position of the pattern; a value of 1 returns the starting position of the next character following the match.

This function returns the starting position of a pattern, so it works much like the familiar INSTR function. The main difference between the two functions is that REGEXP_INSTR lets you specify a pattern instead of a specific search string; thus providing greater versatility.

Parameters
source_string – The string to search.
pattern – The regular expression to match.
start_position – The position to begin the search from.
occurrence – Defaulting to 1, this parameter describes how many occurrences to look for.
return_option –
match_parameter – The default value of the return_option is 0, which returns the starting position of the pattern; a value of 1 returns the starting position of the next character following the match.
Returns

This function returns the a substring matching the regular expression.

Examples
Examples

This example uses REGEXP_INSTR() to return the starting position of the five-digit zip-code pattern within the string Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. If the regular expression is written as :digit:?{5}, you will get the house number's starting position instead of the zip code's, because 10045 is the first occurrence of five consecutive digits. Therefore, you must anchor the expression to the end of the line, as indicated with the $ metacharacter, and the function will display the starting position of the zip code regardless of the number of digits for the house number.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
       '[[:digit:]]{5}\$')
       AS rx_instr;
  RX_INSTR
----------
        45
See Also

regexp_match()

regexp_like()

regexp_parse()

regexp_replace()

regexp_substr()