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
Phrases
RDF data
Remote SQL Data Source
Replication
SOAP
SQL
String
ascii
blob_to_string
blob_to_string_outpu...
chr
ends_with
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
starts_with
strcasestr
strchr
strcontains
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
VAD
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web & Internet
XML
XPATH & XQUERY

Functions Index

regexp_replace

Replaces occurrence(s) of the matching pattern in the source_string with a specified replace_string.
varchar regexp_replace (in source_string any, in pattern any, [in replace_string varchar], [in position integer], [in occurrence any], [in match_parameter integer]);
Description

This function replaces occurrence(s) of the matching pattern in the source_string with a specified replace_string, allowing complex search-and-replace operations. The traditional REPLACE SQL function substitutes one string with another. Assume your data has extraneous spaces in the text and you would like to replace them with a single space. With the REPLACE function, you would need to list exactly how many spaces you want to replace. However, the number of extra spaces may not be the same everywhere in the text.

Parameters
source_string –
pattern – The regular expression to match.
replace_string – By default an empty string, ''.
position – By default this is set to 1.
occurrence – By default this is set to 1. If you specify occurrence as DB NULL value it'll replace all the occurrences of the pattern in the string
match_parameter – By default this is set to null.
Returns

This function returns the a substring matching the regular expression.

Examples
Examples

This example has three spaces between Joe and Smith. The REPLACE function's parameter specifies that two spaces should be replaced with one space. In this case, the result leaves an extra space where there were three spaces in the original string between Joe and Smith.

SELECT
  REPLACE('Joe  Smith', '  ', ' ') AS REPLACE_NORMAL,
  REGEXP_REPLACE('Joe        Smith', '( ){2,}', ' ') AS REGEXP_REPLACE;

REPLACE_NORMAL              REPLACE_REGEXP
VARCHAR                     VARCHAR
______________________________________________

Joe Smith                   Joe Smith
See Also

regexp_match()

regexp_like()

regexp_instr()

regexp_parse()

regexp_substr()