0.58/functions/regexp.html


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    
    <title>2.4. Regular Expression Functions &mdash; Presto 0.58 Documentation</title>
    
    <link rel="stylesheet" href="../_static/presto.css" type="text/css" />
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    
    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    '../',
        VERSION:     '0.58',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true
      };
    </script>
    <script type="text/javascript" src="../_static/jquery.js"></script>
    <script type="text/javascript" src="../_static/underscore.js"></script>
    <script type="text/javascript" src="../_static/doctools.js"></script>
    <link rel="top" title="Presto 0.58 Documentation" href="../index.html" />
    <link rel="up" title="2. Functions and Operators" href="../functions.html" />
    <link rel="next" title="2.5. JSON Functions" href="json.html" />
    <link rel="prev" title="2.3. Date and Time Functions and Operators" href="datetime.html" /> 
  </head>
  <body>
<div class="header">
    <h1 class="heading"><a href="../index.html">
        <span>Presto 0.58 Documentation</span></a></h1>
    <h2 class="heading"><span>2.4. Regular Expression Functions</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="datetime.html">2.3. Date and Time Functions and Operators</a>
    </span>
    <span class="right">
        <a href="json.html">2.5. JSON Functions</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="regular-expression-functions">
<h1>2.4. Regular Expression Functions</h1>
<p>All of the regular expression functions use the <a class="reference external" href="http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html">Java pattern</a> syntax.</p>
<blockquote>
<div></div></blockquote>
<dl class="function">
<dt id="regexp_extract">
<tt class="descname">regexp_extract</tt><big>(</big><em>string</em>, <em>pattern</em><big>)</big> &rarr; varchar</dt>
<dd><p>Returns the first substring matched by the regular expression <tt class="docutils literal"><span class="pre">pattern</span></tt>
in <tt class="docutils literal"><span class="pre">string</span></tt>.</p>
</dd></dl>

<dl class="function">
<dt>
<tt class="descname">regexp_extract</tt><big>(</big><em>string</em>, <em>pattern</em>, <em>group</em><big>)</big> &rarr; varchar</dt>
<dd><p>Finds the first occurrence of the regular expression <tt class="docutils literal"><span class="pre">pattern</span></tt> in
<tt class="docutils literal"><span class="pre">string</span></tt> and returns the <a class="reference external" href="http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html#gnumber">capturing group number</a> <tt class="docutils literal"><span class="pre">group</span></tt>.</p>
</dd></dl>

<dl class="function">
<dt id="regexp_like">
<tt class="descname">regexp_like</tt><big>(</big><em>string</em>, <em>pattern</em><big>)</big> &rarr; boolean</dt>
<dd><p>Evaluates the regular expression <tt class="docutils literal"><span class="pre">pattern</span></tt> and determines if it is
contained within <tt class="docutils literal"><span class="pre">string</span></tt>.</p>
<p>This function is similar to the <tt class="docutils literal"><span class="pre">LIKE</span></tt> operator, expect that the
pattern only needs to be contained within <tt class="docutils literal"><span class="pre">string</span></tt>, rather than
needing to match all of <tt class="docutils literal"><span class="pre">string</span></tt>. In other words, this performs a
<em>contains</em> operation rather than a <em>match</em> operation. You can match
the entire string by anchoring the pattern using <tt class="docutils literal"><span class="pre">^</span></tt> and <tt class="docutils literal"><span class="pre">$</span></tt>.</p>
</dd></dl>

<dl class="function">
<dt id="regexp_replace">
<tt class="descname">regexp_replace</tt><big>(</big><em>string</em>, <em>pattern</em><big>)</big> &rarr; varchar</dt>
<dd><p>Removes every instance of the substring matched by the regular expression
<tt class="docutils literal"><span class="pre">pattern</span></tt> from <tt class="docutils literal"><span class="pre">string</span></tt>.</p>
</dd></dl>

<dl class="function">
<dt>
<tt class="descname">regexp_replace</tt><big>(</big><em>string</em>, <em>pattern</em>, <em>replacement</em><big>)</big> &rarr; varchar</dt>
<dd><p>Replaces every instance of the substring matched by the regular expression
<tt class="docutils literal"><span class="pre">pattern</span></tt> in <tt class="docutils literal"><span class="pre">string</span></tt> with <tt class="docutils literal"><span class="pre">replacement</span></tt>. <a class="reference external" href="http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html#cg">Capturing groups</a> can be
referenced in <tt class="docutils literal"><span class="pre">replacement</span></tt> using <tt class="docutils literal"><span class="pre">$g</span></tt> for a numbered group or
<tt class="docutils literal"><span class="pre">${name}</span></tt> for a named group. A dollar sign (<tt class="docutils literal"><span class="pre">$</span></tt>) may be included in the
replacement by escaping it with a backslash (<tt class="docutils literal"><span class="pre">\$</span></tt>).</p>
</dd></dl>

</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="datetime.html">2.3. Date and Time Functions and Operators</a>
    </span>
    <span class="right">
        <a href="json.html">2.5. JSON Functions</a> &raquo;
    </span>
</p>

</div>

    <div class="footer">
        &copy; Copyright 2013, Facebook.
      Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
    </div>
  </body>
</html>