323/functions/string.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>7.9. String Functions and Operators &#8212; Presto 323 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:     '323',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true,
        SOURCELINK_SUFFIX: '.txt'
      };
    </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="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="7.10. Regular Expression Functions" href="regexp.html" />
    <link rel="prev" title="7.8. Decimal Functions and Operators" href="decimal.html" /> 
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-133457846-1"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-133457846-1');
    </script>
  </head>
  <body>
<div class="header">
    <h1 class="heading"><a href="../index.html">
        <span>Presto 323 Documentation</span></a></h1>
    <h2 class="heading"><span>7.9. String Functions and Operators</span></h2>
</div>
<div class="topnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="decimal.html">7.8. Decimal Functions and Operators</a>
    </span>
    <span class="right">
        <a href="regexp.html">7.10. Regular Expression Functions</a> &raquo;
    </span>
</p>

</div>
<div class="content">
    
  <div class="section" id="string-functions-and-operators">
<h1>7.9. String Functions and Operators</h1>
<div class="section" id="string-operators">
<h2>String Operators</h2>
<p>The <code class="docutils literal"><span class="pre">||</span></code> operator performs concatenation.</p>
</div>
<div class="section" id="string-functions">
<h2>String Functions</h2>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p>These functions assume that the input strings contain valid UTF-8 encoded
Unicode code points.  There are no explicit checks for valid UTF-8 and
the functions may return incorrect results on invalid UTF-8.
Invalid UTF-8 data can be corrected with <a class="reference internal" href="#from_utf8" title="from_utf8"><code class="xref py py-func docutils literal"><span class="pre">from_utf8()</span></code></a>.</p>
<p>Additionally, the functions operate on Unicode code points and not user
visible <em>characters</em> (or <em>grapheme clusters</em>).  Some languages combine
multiple code points into a single user-perceived <em>character</em>, the basic
unit of a writing system for a language, but the functions will treat each
code point as a separate unit.</p>
<p class="last">The <a class="reference internal" href="#lower" title="lower"><code class="xref py py-func docutils literal"><span class="pre">lower()</span></code></a> and <a class="reference internal" href="#upper" title="upper"><code class="xref py py-func docutils literal"><span class="pre">upper()</span></code></a> functions do not perform
locale-sensitive, context-sensitive, or one-to-many mappings required for
some languages. Specifically, this will return incorrect results for
Lithuanian, Turkish and Azeri.</p>
</div>
<dl class="function">
<dt id="chr">
<code class="descname">chr</code><span class="sig-paren">(</span><em>n</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns the Unicode code point <code class="docutils literal"><span class="pre">n</span></code> as a single character string.</p>
</dd></dl>

<dl class="function">
<dt id="codepoint">
<code class="descname">codepoint</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; integer</dt>
<dd><p>Returns the Unicode code point of the only character of <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="concat">
<code class="descname">concat</code><span class="sig-paren">(</span><em>string1</em>, <em>...</em>, <em>stringN</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns the concatenation of <code class="docutils literal"><span class="pre">string1</span></code>, <code class="docutils literal"><span class="pre">string2</span></code>, <code class="docutils literal"><span class="pre">...</span></code>, <code class="docutils literal"><span class="pre">stringN</span></code>.
This function provides the same functionality as the
SQL-standard concatenation operator (<code class="docutils literal"><span class="pre">||</span></code>).</p>
</dd></dl>

<dl class="function">
<dt id="hamming_distance">
<code class="descname">hamming_distance</code><span class="sig-paren">(</span><em>string1</em>, <em>string2</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the Hamming distance of <code class="docutils literal"><span class="pre">string1</span></code> and <code class="docutils literal"><span class="pre">string2</span></code>,
i.e. the number of positions at which the corresponding characters are different.
Note that the two strings must have the same length.</p>
</dd></dl>

<dl class="function">
<dt id="length">
<code class="descname">length</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the length of <code class="docutils literal"><span class="pre">string</span></code> in characters.</p>
</dd></dl>

<dl class="function">
<dt id="levenshtein_distance">
<code class="descname">levenshtein_distance</code><span class="sig-paren">(</span><em>string1</em>, <em>string2</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the Levenshtein edit distance of <code class="docutils literal"><span class="pre">string1</span></code> and <code class="docutils literal"><span class="pre">string2</span></code>,
i.e. the minimum number of single-character edits (insertions,
deletions or substitutions) needed to change <code class="docutils literal"><span class="pre">string1</span></code> into <code class="docutils literal"><span class="pre">string2</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="lower">
<code class="descname">lower</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Converts <code class="docutils literal"><span class="pre">string</span></code> to lowercase.</p>
</dd></dl>

<dl class="function">
<dt id="lpad">
<code class="descname">lpad</code><span class="sig-paren">(</span><em>string</em>, <em>size</em>, <em>padstring</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Left pads <code class="docutils literal"><span class="pre">string</span></code> to <code class="docutils literal"><span class="pre">size</span></code> characters with <code class="docutils literal"><span class="pre">padstring</span></code>.
If <code class="docutils literal"><span class="pre">size</span></code> is less than the length of <code class="docutils literal"><span class="pre">string</span></code>, the result is
truncated to <code class="docutils literal"><span class="pre">size</span></code> characters. <code class="docutils literal"><span class="pre">size</span></code> must not be negative
and <code class="docutils literal"><span class="pre">padstring</span></code> must be non-empty.</p>
</dd></dl>

<dl class="function">
<dt id="ltrim">
<code class="descname">ltrim</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Removes leading whitespace from <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="replace">
<code class="descname">replace</code><span class="sig-paren">(</span><em>string</em>, <em>search</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Removes all instances of <code class="docutils literal"><span class="pre">search</span></code> from <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">replace</code><span class="sig-paren">(</span><em>string</em>, <em>search</em>, <em>replace</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Replaces all instances of <code class="docutils literal"><span class="pre">search</span></code> with <code class="docutils literal"><span class="pre">replace</span></code> in <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="reverse">
<code class="descname">reverse</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns <code class="docutils literal"><span class="pre">string</span></code> with the characters in reverse order.</p>
</dd></dl>

<dl class="function">
<dt id="rpad">
<code class="descname">rpad</code><span class="sig-paren">(</span><em>string</em>, <em>size</em>, <em>padstring</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Right pads <code class="docutils literal"><span class="pre">string</span></code> to <code class="docutils literal"><span class="pre">size</span></code> characters with <code class="docutils literal"><span class="pre">padstring</span></code>.
If <code class="docutils literal"><span class="pre">size</span></code> is less than the length of <code class="docutils literal"><span class="pre">string</span></code>, the result is
truncated to <code class="docutils literal"><span class="pre">size</span></code> characters. <code class="docutils literal"><span class="pre">size</span></code> must not be negative
and <code class="docutils literal"><span class="pre">padstring</span></code> must be non-empty.</p>
</dd></dl>

<dl class="function">
<dt id="rtrim">
<code class="descname">rtrim</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Removes trailing whitespace from <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="split">
<code class="descname">split</code><span class="sig-paren">(</span><em>string</em>, <em>delimiter) -&gt; array(varchar</em><span class="sig-paren">)</span></dt>
<dd><p>Splits <code class="docutils literal"><span class="pre">string</span></code> on <code class="docutils literal"><span class="pre">delimiter</span></code> and returns an array.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">split</code><span class="sig-paren">(</span><em>string</em>, <em>delimiter</em>, <em>limit) -&gt; array(varchar</em><span class="sig-paren">)</span></dt>
<dd><p>Splits <code class="docutils literal"><span class="pre">string</span></code> on <code class="docutils literal"><span class="pre">delimiter</span></code> and returns an array of size at most
<code class="docutils literal"><span class="pre">limit</span></code>. The last element in the array always contain everything
left in the <code class="docutils literal"><span class="pre">string</span></code>. <code class="docutils literal"><span class="pre">limit</span></code> must be a positive number.</p>
</dd></dl>

<dl class="function">
<dt id="split_part">
<code class="descname">split_part</code><span class="sig-paren">(</span><em>string</em>, <em>delimiter</em>, <em>index</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Splits <code class="docutils literal"><span class="pre">string</span></code> on <code class="docutils literal"><span class="pre">delimiter</span></code> and returns the field <code class="docutils literal"><span class="pre">index</span></code>.
Field indexes start with <code class="docutils literal"><span class="pre">1</span></code>. If the index is larger than than
the number of fields, then null is returned.</p>
</dd></dl>

<dl class="function">
<dt id="split_to_map">
<code class="descname">split_to_map</code><span class="sig-paren">(</span><em>string</em>, <em>entryDelimiter</em>, <em>keyValueDelimiter</em><span class="sig-paren">)</span> &#x2192; map&lt;varchar, varchar&gt;</dt>
<dd><p>Splits <code class="docutils literal"><span class="pre">string</span></code> by <code class="docutils literal"><span class="pre">entryDelimiter</span></code> and <code class="docutils literal"><span class="pre">keyValueDelimiter</span></code> and returns a map.
<code class="docutils literal"><span class="pre">entryDelimiter</span></code> splits <code class="docutils literal"><span class="pre">string</span></code> into key-value pairs. <code class="docutils literal"><span class="pre">keyValueDelimiter</span></code> splits
each pair into key and value.</p>
</dd></dl>

<dl class="function">
<dt id="split_to_multimap">
<code class="descname">split_to_multimap</code><span class="sig-paren">(</span><em>string</em>, <em>entryDelimiter</em>, <em>keyValueDelimiter) -&gt; map(varchar</em>, <em>array(varchar)</em><span class="sig-paren">)</span></dt>
<dd><p>Splits <code class="docutils literal"><span class="pre">string</span></code> by <code class="docutils literal"><span class="pre">entryDelimiter</span></code> and <code class="docutils literal"><span class="pre">keyValueDelimiter</span></code> and returns a map
containing an array of values for each unique key. <code class="docutils literal"><span class="pre">entryDelimiter</span></code> splits <code class="docutils literal"><span class="pre">string</span></code>
into key-value pairs. <code class="docutils literal"><span class="pre">keyValueDelimiter</span></code> splits each pair into key and value. The
values for each key will be in the same order as they appeared in <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="strpos">
<code class="descname">strpos</code><span class="sig-paren">(</span><em>string</em>, <em>substring</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the starting position of the first instance of <code class="docutils literal"><span class="pre">substring</span></code> in
<code class="docutils literal"><span class="pre">string</span></code>. Positions start with <code class="docutils literal"><span class="pre">1</span></code>. If not found, <code class="docutils literal"><span class="pre">0</span></code> is returned.</p>
</dd></dl>

<dl class="function">
<dt id="position">
<code class="descname">position</code><span class="sig-paren">(</span><em>substring IN string</em><span class="sig-paren">)</span> &#x2192; bigint</dt>
<dd><p>Returns the starting position of the first instance of <code class="docutils literal"><span class="pre">substring</span></code> in
<code class="docutils literal"><span class="pre">string</span></code>. Positions start with <code class="docutils literal"><span class="pre">1</span></code>. If not found, <code class="docutils literal"><span class="pre">0</span></code> is returned.</p>
</dd></dl>

<dl class="function">
<dt id="substr">
<code class="descname">substr</code><span class="sig-paren">(</span><em>string</em>, <em>start</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns the rest of <code class="docutils literal"><span class="pre">string</span></code> from the starting position <code class="docutils literal"><span class="pre">start</span></code>.
Positions start with <code class="docutils literal"><span class="pre">1</span></code>. A negative starting position is interpreted
as being relative to the end of the string.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">substr</code><span class="sig-paren">(</span><em>string</em>, <em>start</em>, <em>length</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns a substring from <code class="docutils literal"><span class="pre">string</span></code> of length <code class="docutils literal"><span class="pre">length</span></code> from the starting
position <code class="docutils literal"><span class="pre">start</span></code>. Positions start with <code class="docutils literal"><span class="pre">1</span></code>. A negative starting
position is interpreted as being relative to the end of the string.</p>
</dd></dl>

<dl class="function">
<dt id="trim">
<code class="descname">trim</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Removes leading and trailing whitespace from <code class="docutils literal"><span class="pre">string</span></code>.</p>
</dd></dl>

<dl class="function">
<dt id="upper">
<code class="descname">upper</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Converts <code class="docutils literal"><span class="pre">string</span></code> to uppercase.</p>
</dd></dl>

<dl class="function">
<dt id="word_stem">
<code class="descname">word_stem</code><span class="sig-paren">(</span><em>word</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns the stem of <code class="docutils literal"><span class="pre">word</span></code> in the English language.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">word_stem</code><span class="sig-paren">(</span><em>word</em>, <em>lang</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Returns the stem of <code class="docutils literal"><span class="pre">word</span></code> in the <code class="docutils literal"><span class="pre">lang</span></code> language.</p>
</dd></dl>

</div>
<div class="section" id="unicode-functions">
<h2>Unicode Functions</h2>
<dl class="function">
<dt id="normalize">
<code class="descname">normalize</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Transforms <code class="docutils literal"><span class="pre">string</span></code> with NFC normalization form.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">normalize</code><span class="sig-paren">(</span><em>string</em>, <em>form</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Transforms <code class="docutils literal"><span class="pre">string</span></code> with the specified normalization form.
<code class="docutils literal"><span class="pre">form</span></code> must be be one of the following keywords:</p>
<table border="1" class="docutils">
<colgroup>
<col width="11%" />
<col width="89%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">Form</th>
<th class="head">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><code class="docutils literal"><span class="pre">NFD</span></code></td>
<td>Canonical Decomposition</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">NFC</span></code></td>
<td>Canonical Decomposition, followed by Canonical Composition</td>
</tr>
<tr class="row-even"><td><code class="docutils literal"><span class="pre">NFKD</span></code></td>
<td>Compatibility Decomposition</td>
</tr>
<tr class="row-odd"><td><code class="docutils literal"><span class="pre">NFKC</span></code></td>
<td>Compatibility Decomposition, followed by Canonical Composition</td>
</tr>
</tbody>
</table>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">This SQL-standard function has special syntax and requires
specifying <code class="docutils literal"><span class="pre">form</span></code> as a keyword, not as a string.</p>
</div>
</dd></dl>

<dl class="function">
<dt id="to_utf8">
<code class="descname">to_utf8</code><span class="sig-paren">(</span><em>string</em><span class="sig-paren">)</span> &#x2192; varbinary</dt>
<dd><p>Encodes <code class="docutils literal"><span class="pre">string</span></code> into a UTF-8 varbinary representation.</p>
</dd></dl>

<dl class="function">
<dt id="from_utf8">
<code class="descname">from_utf8</code><span class="sig-paren">(</span><em>binary</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Decodes a UTF-8 encoded string from <code class="docutils literal"><span class="pre">binary</span></code>. Invalid UTF-8 sequences
are replaced with the Unicode replacement character <code class="docutils literal"><span class="pre">U+FFFD</span></code>.</p>
</dd></dl>

<dl class="function">
<dt>
<code class="descname">from_utf8</code><span class="sig-paren">(</span><em>binary</em>, <em>replace</em><span class="sig-paren">)</span> &#x2192; varchar</dt>
<dd><p>Decodes a UTF-8 encoded string from <code class="docutils literal"><span class="pre">binary</span></code>. Invalid UTF-8 sequences
are replaced with <code class="docutils literal"><span class="pre">replace</span></code>. The replacement string <code class="docutils literal"><span class="pre">replace</span></code> must either
be a single character or empty (in which case invalid characters are
removed).</p>
</dd></dl>

</div>
</div>


</div>
<div class="bottomnav">
    
<p class="nav">
    <span class="left">
        &laquo; <a href="decimal.html">7.8. Decimal Functions and Operators</a>
    </span>
    <span class="right">
        <a href="regexp.html">7.10. Regular Expression Functions</a> &raquo;
    </span>
</p>

</div>

    <div class="footer" role="contentinfo">
    </div>
  </body>
</html>