<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="Function: regexp_replace - Virtuoso Functions Guide" />
<meta name="dc.subject" content="Function: regexp_replace - Virtuoso Functions Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="functions.html" title="Chapter Contents" />
<link rel="prev" href="appendixa.html" title="Appendix" />
<link rel="next" href="functions.html#.html" title="" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>Function: regexp_replace - Virtuoso Functions Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="fn_regexp_replace" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>22. Virtuoso Functions Guide - regexp_replace</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="functions.html">Chapter Contents</a> | <a class="link" href="fn_regexp_substr.html" title="regexp_substr">Prev</a> | <a class="link" href="fn_regexp_instr.html" title="regexp_instr">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div>
<a class="selected" href="functions.html">Virtuoso Functions Guide</a>
</div>
<br />
<div>
<a href="functions.html#admin">Administration</a>
</div>
<div>
<a href="functions.html#aggr">Aggregate Functions</a>
</div>
<div>
<a href="functions.html#array">Array Manipulation</a>
</div>
<div>
<a href="functions.html#bpel">BPEL APIs</a>
</div>
<div>
<a href="functions.html#backup">Backup</a>
</div>
<div>
<a href="functions.html#compression">Compression</a>
</div>
<div>
<a href="functions.html#cursors">Cursor</a>
</div>
<div>
<a href="functions.html#time">Date & Time Manipulation</a>
</div>
<div>
<a href="functions.html#debug">Debug</a>
</div>
<div>
<a href="functions.html#dict">Dictionary Manipulation</a>
</div>
<div>
<a href="functions.html#encoding">Encoding & Decoding</a>
</div>
<div>
<a href="functions.html#file">File Manipulation</a>
</div>
<div>
<a href="functions.html#ft">Free Text</a>
</div>
<div>
<a href="functions.html#hash">Hashing / Cryptographic</a>
</div>
<div>
<a href="functions.html#ldap">LDAP</a>
</div>
<div>
<a href="functions.html#localization">Locale</a>
</div>
<div>
<a href="functions.html#mail">Mail</a>
</div>
<div>
<a href="functions.html#misc">Miscellaneous</a>
</div>
<div>
<a href="functions.html#number">Number</a>
</div>
<div>
<a href="functions.html#phrz">Phrases</a>
</div>
<div>
<a href="functions.html#rdf">RDF data</a>
</div>
<div>
<a href="functions.html#rmt">Remote SQL Data Source</a>
</div>
<div>
<a href="functions.html#repl">Replication</a>
</div>
<div>
<a href="functions.html#soap">SOAP</a>
</div>
<div>
<a href="functions.html#sql">SQL</a>
</div>
<div>
<a class="selected" href="functions.html#string">String</a>
</div>
<div class="selected">
<div>
<a href="fn_ascii.html">ascii</a>
</div>
<div>
<a href="fn_blob_to_string.html">blob_to_string</a>
</div>
<div>
<a href="fn_blob_to_string_output.html">blob_to_string_outpu...</a>
</div>
<div>
<a href="fn_chr.html">chr</a>
</div>
<div>
<a href="fn_ends_with.html">ends_with</a>
</div>
<div>
<a href="fn_initcap.html">initcap</a>
</div>
<div>
<a href="fn_isblob.html">isblob</a>
</div>
<div>
<a href="fn_isstring.html">isstring</a>
</div>
<div>
<a href="fn_lcase.html">lcase</a>
</div>
<div>
<a href="fn_left.html">left</a>
</div>
<div>
<a href="fn_length.html">length</a>
</div>
<div>
<a href="fn_locate.html">locate</a>
</div>
<div>
<a href="fn_ltrim.html">ltrim</a>
</div>
<div>
<a href="fn_make_string.html">make_string</a>
</div>
<div>
<a href="fn_regexp_instr.html">regexp_instr</a>
</div>
<div>
<a href="fn_regexp_like.html">regexp_like</a>
</div>
<div>
<a href="fn_regexp_match.html">regexp_match</a>
</div>
<div>
<a href="fn_regexp_parse.html">regexp_parse</a>
</div>
<div>
<a class="selected" href="fn_regexp_replace.html">regexp_replace</a>
</div>
<div>
<a href="fn_regexp_substr.html">regexp_substr</a>
</div>
<div>
<a href="fn_repeat.html">repeat</a>
</div>
<div>
<a href="fn_replace.html">replace</a>
</div>
<div>
<a href="fn_right.html">right</a>
</div>
<div>
<a href="fn_rtrim.html">rtrim</a>
</div>
<div>
<a href="fn_search_excerpt.html">search_excerpt</a>
</div>
<div>
<a href="fn_serialize.html">serialize</a>
</div>
<div>
<a href="fn_space.html">space</a>
</div>
<div>
<a href="fn_split_and_decode.html">split_and_decode</a>
</div>
<div>
<a href="fn_sprintf.html">sprintf</a>
</div>
<div>
<a href="fn_sprintf_inverse.html">sprintf_inverse</a>
</div>
<div>
<a href="fn_sprintf_iri.html">sprintf_iri</a>
</div>
<div>
<a href="fn_sprintf_iri_or_null.html">sprintf_iri_or_null</a>
</div>
<div>
<a href="fn_sprintf_or_null.html">sprintf_or_null</a>
</div>
<div>
<a href="fn_starts_with.html">starts_with</a>
</div>
<div>
<a href="fn_strcasestr.html">strcasestr</a>
</div>
<div>
<a href="fn_strchr.html">strchr</a>
</div>
<div>
<a href="fn_strcontains.html">strcontains</a>
</div>
<div>
<a href="fn_string_output.html">string_output</a>
</div>
<div>
<a href="fn_string_output_flush.html">string_output_flush</a>
</div>
<div>
<a href="fn_string_output_gz_compress.html">string_output_gz_com...</a>
</div>
<div>
<a href="fn_string_output_string.html">string_output_string</a>
</div>
<div>
<a href="fn_string_to_file.html">string_to_file</a>
</div>
<div>
<a href="fn_strrchr.html">strrchr</a>
</div>
<div>
<a href="fn_strstr.html">strstr</a>
</div>
<div>
<a href="fn_subseq.html">subseq</a>
</div>
<div>
<a href="fn_substring.html">substring</a>
</div>
<div>
<a href="fn_tmp_file_name.html">tmp_file_name</a>
</div>
<div>
<a href="fn_trim.html">trim</a>
</div>
<div>
<a href="fn_ucase.html">ucase</a>
</div>
<div>
<a href="fn_upper.html">upper</a>
</div>
</div>
<div>
<a href="functions.html#txn">Transaction</a>
</div>
<div>
<a href="functions.html#type">Type Mapping</a>
</div>
<div>
<a href="functions.html#uddi">UDDI</a>
</div>
<div>
<a href="functions.html#udt">User Defined Types & The CLR</a>
</div>
<div>
<a href="functions.html#vad">VAD</a>
</div>
<div>
<a href="functions.html#jvmpl">Virtuoso Java PL API</a>
</div>
<div>
<a href="functions.html#bif">Virtuoso Server Extension Interface (VSEI)</a>
</div>
<div>
<a href="functions.html#ws">Web & Internet</a>
</div>
<div>
<a href="functions.html#xml">XML</a>
</div>
<div>
<a href="functions.html#XPATH">XPATH & XQUERY</a>
</div>
<br />
<div>
<a href="functionidx.html">Functions Index</a>
</div>
<br />
</div>
<div id="text">
<h2>regexp_replace</h2>
<div class="refpurpose">Replaces occurrence(s) of the matching pattern in the source_string with a specified replace_string.</div>
<div class="funcsynopsis">
<span class="funcdef">varchar <span class="function">regexp_replace</span>
</span>
(<span class="paramdef">in <span class="parameter">source_string</span> any</span>,
<span class="paramdef">in <span class="parameter">pattern</span> any</span>,
<span class="paramdefoptional">[in <span class="optional">replace_string</span> varchar]</span>,
<span class="paramdefoptional">[in <span class="optional">position</span> integer]</span>,
<span class="paramdefoptional">[in <span class="optional">occurrence</span> any]</span>,
<span class="paramdefoptional">[in <span class="optional">match_parameter</span> integer]</span>);
</div>
<div class="refsect1">
<div class="refsect1title">Description</div>
<p>This function replaces occurrence(s) of the matching
<span class="computeroutput">pattern</span> in the <span class="computeroutput">source_string</span> with
a specified <span class="computeroutput">replace_string</span>, 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. </p>
</div>
<div class="refsect1">
<div class="refsect1title">Parameters</div>
<div class="refsect2">
<span class="refsect2title">source_string – </span>
</div>
<div class="refsect2">
<span class="refsect2title">pattern – </span>
The regular expression to match.</div>
<div class="refsect2">
<span class="refsect2title">replace_string – </span>
By default an empty string, ''.</div>
<div class="refsect2">
<span class="refsect2title">position – </span>
By default this is set to 1.</div>
<div class="refsect2">
<span class="refsect2title">occurrence – </span>
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</div>
<div class="refsect2">
<span class="refsect2title">match_parameter – </span>
By default this is set to null.</div>
</div>
<div class="refsect1">
<div class="refsect1title">Returns</div>
<p>This function returns the a substring matching the regular expression.</p>
</div>
<div class="refsect1">
<div class="refsect1title">Examples</div>
<a name="ex_regexp_replace" />
<div class="example">
<div class="exampletitle">Examples</div>
<p>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. </p>
<div>
<pre class="programlisting">
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
</pre>
</div>
</div>
</div>
<div class="refsect1">
<div class="refsect1title">See Also</div>
<p>
<a href="fn_regexp_match.html">regexp_match()</a>
</p>
<p>
<a href="fn_regexp_like.html">regexp_like()</a>
</p>
<p>
<a href="fn_regexp_instr.html">regexp_instr()</a>
</p>
<p>
<a href="fn_regexp_parse.html">regexp_parse()</a>
</p>
<p>
<a href="fn_regexp_substr.html">regexp_substr()</a>
</p>
</div>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
467
