# 22. Virtuoso Functions Guide - regexp_replace

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_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 occurences 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()