http://git-wip-us.apache.org/repos/asf/asterixdb-site/blob/d5b11d83/content/docs/0.9.0/sqlpp/builtins.html ---------------------------------------------------------------------- diff --git a/content/docs/0.9.0/sqlpp/builtins.html b/content/docs/0.9.0/sqlpp/builtins.html new file mode 100644 index 0000000..8e368ac --- /dev/null +++ b/content/docs/0.9.0/sqlpp/builtins.html @@ -0,0 +1,8494 @@ +<!DOCTYPE html> +<!-- + | Generated by Apache Maven Doxia at 2017-01-25 + | Rendered using Apache Maven Fluido Skin 1.3.0 +--> +<html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"> + <head> + <meta charset="UTF-8" /> + <meta name="viewport" content="width=device-width, initial-scale=1.0" /> + <meta name="Date-Revision-yyyymmdd" content="20170125" /> + <meta http-equiv="Content-Language" content="en" /> + <title>AsterixDB – Builtin Functions</title> + <link rel="stylesheet" href="../css/apache-maven-fluido-1.3.0.min.css" /> + <link rel="stylesheet" href="../css/site.css" /> + <link rel="stylesheet" href="../css/print.css" media="print" /> + + + <script type="text/javascript" src="../js/apache-maven-fluido-1.3.0.min.js"></script> + + + +<script>(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){ + (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o), + m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m) + })(window,document,'script','//www.google-analytics.com/analytics.js','ga'); + + ga('create', 'UA-41536543-1', 'uci.edu'); + ga('send', 'pageview');</script> + + </head> + <body class="topBarDisabled"> + + + + + <div class="container-fluid"> + <div id="banner"> + <div class="pull-left"> + <a href=".././" id="bannerLeft"> + <img src="../images/asterixlogo.png" alt="AsterixDB"/> + </a> + </div> + <div class="pull-right"> </div> + <div class="clear"><hr/></div> + </div> + + <div id="breadcrumbs"> + <ul class="breadcrumb"> + + + <li id="publishDate">Last Published: 2017-01-25</li> + + + + <li id="projectVersion" class="pull-right">Version: 0.9.0</li> + + <li class="divider pull-right">|</li> + + <li class="pull-right"> <a href="../index.html" title="Documentation Home"> + Documentation Home</a> + </li> + + </ul> + </div> + + + <div class="row-fluid"> + <div id="leftColumn" class="span3"> + <div class="well sidebar-nav"> + + + <ul class="nav nav-list"> + <li class="nav-header">Get Started - Installation</li> + + <li> + + <a href="../ncservice.html" title="Option 1: using NCService"> + <i class="none"></i> + Option 1: using NCService</a> + </li> + + <li> + + <a href="../install.html" title="Option 2: using Managix"> + <i class="none"></i> + Option 2: using Managix</a> + </li> + + <li> + + <a href="../yarn.html" title="Option 3: using YARN"> + <i class="none"></i> + Option 3: using YARN</a> + </li> + <li class="nav-header">AsterixDB Primer</li> + + <li> + + <a href="../sqlpp/primer-sqlpp.html" title="Option 1: using SQL++"> + <i class="none"></i> + Option 1: using SQL++</a> + </li> + + <li> + + <a href="../aql/primer.html" title="Option 2: using AQL"> + <i class="none"></i> + Option 2: using AQL</a> + </li> + <li class="nav-header">Data Model</li> + + <li> + + <a href="../datamodel.html" title="The Asterix Data Model"> + <i class="none"></i> + The Asterix Data Model</a> + </li> + <li class="nav-header">Queries - SQL++</li> + + <li> + + <a href="../sqlpp/manual.html" title="The SQL++ Query Language"> + <i class="none"></i> + The SQL++ Query Language</a> + </li> + + <li class="active"> + + <a href="#"><i class="none"></i>Builtin Functions</a> + </li> + <li class="nav-header">Queries - AQL</li> + + <li> + + <a href="../aql/manual.html" title="The Asterix Query Language (AQL)"> + <i class="none"></i> + The Asterix Query Language (AQL)</a> + </li> + + <li> + + <a href="../aql/builtins.html" title="Builtin Functions"> + <i class="none"></i> + Builtin Functions</a> + </li> + <li class="nav-header">Advanced Features</li> + + <li> + + <a href="../aql/similarity.html" title="Support of Similarity Queries"> + <i class="none"></i> + Support of Similarity Queries</a> + </li> + + <li> + + <a href="../aql/fulltext.html" title="Support of Full-text Queries"> + <i class="none"></i> + Support of Full-text Queries</a> + </li> + + <li> + + <a href="../aql/externaldata.html" title="Accessing External Data"> + <i class="none"></i> + Accessing External Data</a> + </li> + + <li> + + <a href="../feeds/tutorial.html" title="Support for Data Ingestion"> + <i class="none"></i> + Support for Data Ingestion</a> + </li> + + <li> + + <a href="../udf.html" title="User Defined Functions"> + <i class="none"></i> + User Defined Functions</a> + </li> + + <li> + + <a href="../aql/filters.html" title="Filter-Based LSM Index Acceleration"> + <i class="none"></i> + Filter-Based LSM Index Acceleration</a> + </li> + <li class="nav-header">API/SDK</li> + + <li> + + <a href="../api.html" title="HTTP API"> + <i class="none"></i> + HTTP API</a> + </li> + </ul> + + + + <hr class="divider" /> + + <div id="poweredBy"> + <div class="clear"></div> + <div class="clear"></div> + <div class="clear"></div> + <a href=".././" title="AsterixDB" class="builtBy"> + <img class="builtBy" alt="AsterixDB" src="../images/asterixlogo.png" /> + </a> + </div> + </div> + </div> + + + <div id="bodyColumn" class="span9" > + + <!-- ! Licensed to the Apache Software Foundation (ASF) under one + ! or more contributor license agreements. See the NOTICE file + ! distributed with this work for additional information + ! regarding copyright ownership. The ASF licenses this file + ! to you under the Apache License, Version 2.0 (the + ! "License"); you may not use this file except in compliance + ! with the License. You may obtain a copy of the License at + ! + ! http://www.apache.org/licenses/LICENSE-2.0 + ! + ! Unless required by applicable law or agreed to in writing, + ! software distributed under the License is distributed on an + ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + ! KIND, either express or implied. See the License for the + ! specific language governing permissions and limitations + ! under the License. + ! --><h1>Builtin Functions</h1> +<div class="section"> +<h2><a name="Table_of_Contents"></a><a name="toc" id="toc">Table of Contents</a></h2> + +<ul> + +<li><a href="#NumericFunctions">Numeric Functions</a></li> + +<li><a href="#StringFunctions">String Functions</a></li> + +<li><a href="#BinaryFunctions">Binary Functions</a></li> + +<li><a href="#SpatialFunctions">Spatial Functions</a></li> + +<li><a href="#SimilarityFunctions">Similarity Functions</a></li> + +<li><a href="#TokenizingFunctions">Tokenizing Functions</a></li> + +<li><a href="#TemporalFunctions">Temporal Functions</a></li> + +<li><a href="#ObjectFunctions">Object Functions</a></li> + +<li><a href="#AggregateFunctions">Aggregate Functions (Array Functions)</a></li> + +<li><a href="#ComparisonFunctions">Comparison Functions</a></li> + +<li><a href="#TypeFunctions">Type Functions</a></li> + +<li><a href="#MiscFunctions">Miscellaneous Functions</a></li> +</ul> +<p>The system provides various classes of functions to support operations on numeric, string, spatial, and temporal data. This document explains how to use these functions. <!-- ! Licensed to the Apache Software Foundation (ASF) under one + ! or more contributor license agreements. See the NOTICE file + ! distributed with this work for additional information + ! regarding copyright ownership. The ASF licenses this file + ! to you under the Apache License, Version 2.0 (the + ! "License"); you may not use this file except in compliance + ! with the License. You may obtain a copy of the License at + ! + ! http://www.apache.org/licenses/LICENSE-2.0 + ! + ! Unless required by applicable law or agreed to in writing, + ! software distributed under the License is distributed on an + ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + ! KIND, either express or implied. See the License for the + ! specific language governing permissions and limitations + ! under the License. + ! --></p></div> +<div class="section"> +<h2><a name="Numeric_Functions"></a><a name="NumericFunctions" id="NumericFunctions">Numeric Functions</a></h2> +<div class="section"> +<h3><a name="abs"></a>abs</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>abs(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the absolute value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>The absolute value of the argument with the same type as the input argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": abs(2013), "v2": abs(-4036), "v3": abs(0), "v4": abs(float("-2013.5")), "v5": abs(double("-2013.593823748327284")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2013, "v2": 4036, "v3": 0, "v4": 2013.5, "v5": 2013.5938237483274 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="acos"></a>acos</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>acos(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the arc cosine value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> arc cosine in radians for the argument, if the argument is in the range of -1 (inclusive) to 1 (inclusive),</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error,</li> + +<li>NaN for other legitimate numeric values.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": acos(1), "v2": acos(2), "v3": acos(0), "v4": acos(float("0.5")), "v5": acos(double("-0.5")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.0, "v2": NaN, "v3": 1.5707963267948966, "v4": 1.0471975511965979, "v5": 2.0943951023931957 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="asin"></a>asin</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>asin(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the arc sine value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> arc sin in radians for the argument, if the argument is in the range of -1 (inclusive) to 1 (inclusive),</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error,</li> + +<li>NaN for other legitimate numeric values.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": asin(1), "v2": asin(2), "v3": asin(0), "v4": asin(float("0.5")), "v5": asin(double("-0.5")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 1.5707963267948966, "v2": NaN, "v3": 0.0, "v4": 0.5235987755982989, "v5": -0.5235987755982989 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="atan"></a>atan</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>atan(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the arc tangent value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> arc tangent in radians for the argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": atan(1), "v2": atan(2), "v3": atan(0), "v4": atan(float("0.5")), "v5": atan(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.7853981633974483, "v2": 1.1071487177940904, "v3": 0.0, "v4": 0.4636476090008061, "v5": 1.5697963271282298 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="atan2"></a>atan2</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>atan2(numeric_value1, numeric_value2) +</pre></div></div></li> + +<li> +<p>Computes the arc tangent value of numeric_value2/numeric_value1.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value1</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value,</li> + +<li><tt>numeric_value2</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> arc tangent in radians for <tt>numeric_value1</tt> and <tt>numeric_value2</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": atan2(1, 2), "v2": atan2(0, 4), "v3": atan2(float("0.5"), double("-0.5")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.4636476090008061, "v2": 0.0, "v3": 2.356194490192345 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="ceil"></a>ceil</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>ceil(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the smallest (closest to negative infinity) number with no fractional part that is not less than the value of the argument. If the argument is already equal to mathematical integer, then the result is the same as the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>The ceiling value for the given number in the same type as the input argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": ceil(2013), + "v2": ceil(-4036), + "v3": ceil(0.3), + "v4": ceil(float("-2013.2")), + "v5": ceil(double("-2013.893823748327284")) +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2013.0 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="cos"></a>cos</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>cos(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the cosine value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> cosine value for the argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": cos(1), "v2": cos(2), "v3": cos(0), "v4": cos(float("0.5")), "v5": cos(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.5403023058681398, "v2": -0.4161468365471424, "v3": 1.0, "v4": 0.8775825618903728, "v5": 0.562379076290703 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="exp"></a>exp</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>exp(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes e<sup>numeric_value</sup>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>e<sup>numeric_value</sup>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": exp(1), "v2": exp(2), "v3": exp(0), "v4": exp(float("0.5")), "v5": exp(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2.718281828459045, "v2": 7.38905609893065, "v3": 1.0, "v4": 1.6487212707001282, "v5": Infinity } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="floor"></a>floor</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>floor(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the largest (closest to positive infinity) number with no fractional part that is not greater than the value. If the argument is already equal to mathematical integer, then the result is the same as the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>The floor value for the given number in the same type as the input argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": floor(2013), + "v2": floor(-4036), + "v3": floor(0.8), + "v4": floor(float("-2013.2")), + "v5": floor(double("-2013.893823748327284")) +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2013, "v2": -4036, "v3": 0.0, "v4": -2014.0, "v5": -2014.0 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="ln"></a>ln</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>ln(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes log<sub>e</sub>numeric_value.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>log<sub>e</sub>numeric_value,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": ln(1), "v2": ln(2), "v3": ln(0), "v4": ln(float("0.5")), "v5": ln(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.0, "v2": 0.6931471805599453, "v3": -Infinity, "v4": -0.6931471805599453, "v5": 6.907755278982137 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="log"></a>log</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>log(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes log<sub>10</sub>numeric_value.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>log<sub>10</sub>numeric_value,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": log(1), "v2": log(2), "v3": log(0), "v4": log(float("0.5")), "v5": log(double("1000")) }; +</pre></div></div></li> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.0, "v2": 0.3010299956639812, "v3": -Infinity, "v4": -0.3010299956639812, "v5": 3.0 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="power"></a>power</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>power(numeric_value1, numeric_value2) +</pre></div></div></li> + +<li> +<p>Computes numeric_value1<sup>numeric_value2</sup>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value1</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value,</li> + +<li><tt>numeric_value2</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>numeric_value1<sup>numeric_value2</sup>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": power(1, 2), "v3": power(0, 4), "v4": power(float("0.5"), double("-0.5")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 1, "v3": 0, "v4": 1.4142135623730951 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="round"></a>round</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>round(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the number with no fractional part that is closest (and also closest to positive infinity) to the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>The rounded value for the given number in the same type as the input argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": round(2013), + "v2": round(-4036), + "v3": round(0.8), + "v4": round(float("-2013.256")), + "v5": round(double("-2013.893823748327284")) +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2014.0 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="round_half_to_even"></a>round_half_to_even</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>round_half_to_even(numeric_value, [precision]) +</pre></div></div></li> + +<li> +<p>Computes the closest numeric value to <tt>numeric_value</tt> that is a multiple of ten to the power of minus <tt>precision</tt>. <tt>precision</tt> is optional and by default value <tt>0</tt> is used.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + +<li><tt>precision</tt>: an optional <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt> field representing the number of digits in the fraction of the the result</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>The rounded value for the given number in the same type as the input argument,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>a type error will be raised if: + +<ul> + +<li>the first argument is any other non-numeric value,</li> + +<li>or, the second argument is any other non-tinyint, non-smallint, non-integer, or non-bigint value.</li> + </ul></li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": round_half_to_even(2013), + "v2": round_half_to_even(-4036), + "v3": round_half_to_even(0.8), + "v4": round_half_to_even(float("-2013.256")), + "v5": round_half_to_even(double("-2013.893823748327284")), + "v6": round_half_to_even(double("-2013.893823748327284"), 2), + "v7": round_half_to_even(2013, 4), + "v8": round_half_to_even(float("-2013.256"), 5) +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2013, "v2": -4036, "v3": 1.0, "v4": -2013.0, "v5": -2014.0, "v6": -2013.89, "v7": 2013, "v8": -2013.256 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="sign"></a>sign</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>sign(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the sign of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the sign (a <tt>tinyint</tt>) of the argument, -1 for negative values, 0 for 0, and 1 for positive values,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": sign(1), "v2": sign(2), "v3": sign(0), "v4": sign(float("0.5")), "v5": sign(double("-1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 1, "v2": 1, "v3": 0, "v4": 1, "v5": -1 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="sin"></a>sin</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>sin(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the sine value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> sine value for the argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": sin(1), "v2": sin(2), "v3": sin(0), "v4": sin(float("0.5")), "v5": sin(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0.8414709848078965, "v2": 0.9092974268256817, "v3": 0.0, "v4": 0.479425538604203, "v5": 0.8268795405320025 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="sqrt"></a>sqrt</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>sqrt(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the square root of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> square root value for the argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": sqrt(1), "v2": sqrt(2), "v3": sqrt(0), "v4": sqrt(float("0.5")), "v5": sqrt(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 1.0, "v2": 1.4142135623730951, "v3": 0.0, "v4": 0.7071067811865476, "v5": 31.622776601683793 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="tan"></a>tan</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>tan(numeric_value) +</pre></div></div></li> + +<li> +<p>Computes the tangent value of the argument.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> tangent value for the argument,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-numeric input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": tan(1), "v2": tan(2), "v3": tan(0), "v4": tan(float("0.5")), "v5": tan(double("1000")) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 1.5574077246549023, "v2": -2.185039863261519, "v3": 0.0, "v4": 0.5463024898437905, "v5": 1.4703241557027185 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="trunc"></a>trunc</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>trunc(numeric_value, number_digits) +</pre></div></div></li> + +<li> +<p>Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>numeric_value</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt>/<tt>float</tt>/<tt>double</tt> value,</li> + +<li><tt>number_digits</tt>: a <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt> value.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the <tt>double</tt> tangent value for the argument,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is <tt>missing</tt>,</li> + +<li>a type error will be raised if: + +<ul> + +<li>the first argument is any other non-numeric value,</li> + +<li>the second argument is any other non-tinyint, non-smallint, non-integer, and non-bigint value.</li> + </ul></li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": trunc(1, 1), "v2": trunc(2, -2), "v3": trunc(0.122, 2), "v4": trunc(float("11.52"), -1), "v5": trunc(double("1000.5252"), 3) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 1, "v2": 2, "v3": 0.12, "v4": 10.0, "v5": 1000.525 } +</pre></div></div></li> +</ul> +<!-- ! Licensed to the Apache Software Foundation (ASF) under one + ! or more contributor license agreements. See the NOTICE file + ! distributed with this work for additional information + ! regarding copyright ownership. The ASF licenses this file + ! to you under the Apache License, Version 2.0 (the + ! "License"); you may not use this file except in compliance + ! with the License. You may obtain a copy of the License at + ! + ! http://www.apache.org/licenses/LICENSE-2.0 + ! + ! Unless required by applicable law or agreed to in writing, + ! software distributed under the License is distributed on an + ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + ! KIND, either express or implied. See the License for the + ! specific language governing permissions and limitations + ! under the License. + ! --></div></div> +<div class="section"> +<h2><a name="String_Functions"></a><a name="StringFunctions" id="StringFunctions">String Functions</a></h2> +<div class="section"> +<h3><a name="concat"></a>concat</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>concat(string1, string2, ...) +</pre></div></div></li> + +<li> +<p>Returns a concatenated string from arguments.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string1</tt>: a string value,</li> + +<li><tt>string2</tt>: a string value,</li> + +<li>….</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a concatenated string from arguments,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>concat("test ", "driven ", "development"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"test driven development" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="contains"></a>contains</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>contains(string, substring_to_contain) +</pre></div></div></li> + +<li> +<p>Checks whether the string <tt>string</tt> contains the string <tt>substring_to_contain</tt></p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might contain the given substring,</li> + +<li><tt>substring_to_contain</tt> : a target <tt>string</tt> that might be contained.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>boolean</tt> value, <tt>true</tt> if <tt>string</tt> contains <tt>substring_to_contain</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error,</li> + +<li><tt>false</tt> otherwise.</li> + </ul></li> + +<li> +<p>Note: an <a href="similarity.html#UsingIndexesToSupportSimilarityQueries">n_gram index</a> can be utilized for this function.</p></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": contains("I like iphone", "phone"), "v2": contains("one", "phone") }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": true, "v2": false } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="ends_with"></a>ends_with</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>ends_with(string, substring_to_end_with) +</pre></div></div></li> + +<li> +<p>Checks whether the string <tt>string</tt> ends with the string <tt>substring_to_end_with</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might end with the given string,</li> + +<li><tt>substring_to_end_with</tt> : a <tt>string</tt> that might be contained as the ending substring.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>boolean</tt> value, <tt>true</tt> if <tt>string</tt> contains <tt>substring_to_contain</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error,</li> + +<li><tt>false</tt> otherwise.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": ends_with(" love sprint its shortcut_menu is awesome:)", ":)"), + "v2": ends_with(" awsome:)", ":-)") +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": true, "v2": false } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="initcap_or_title"></a>initcap (or title)</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>initcap(string) +</pre></div></div></li> + +<li> +<p>Converts a given string <tt>string</tt> so that the first letter of each word is uppercase and every other letter is lowercase. The function has an alias called “title”.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be converted.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> as the title form of the given <tt>string</tt>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": initcap("ASTERIXDB is here!"), "v2": title("ASTERIXDB is here!") }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": "Asterixdb Is Here!", "v2": "Asterixdb Is Here!" } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="length"></a>length</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>length(string) +</pre></div></div></li> + +<li> +<p>Returns the length of the string <tt>string</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> or <tt>null</tt> that represents the string to be checked.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>an <tt>bigint</tt> that represents the length of <tt>string</tt>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>length("test string"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>11 +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="lower"></a>lower</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>lower(string) +</pre></div></div></li> + +<li> +<p>Converts a given string <tt>string</tt> to its lowercase form.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be converted.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> as the lowercase form of the given <tt>string</tt>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>lower("ASTERIXDB"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"asterixdb" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="ltrim"></a>ltrim</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>ltrim(string[, chars]); +</pre></div></div></li> + +<li> +<p>Returns a new string with all leading characters that appear in <tt>chars</tt> removed. By default, white space is the character to trim.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be trimmed,</li> + +<li><tt>chars</tt> : a <tt>string</tt> that contains characters that are used to trim.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a trimmed, new <tt>string</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> +</ul> + +<ul> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>ltrim("me like iphone", "eml"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>" like iphone" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="position"></a>position</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>position(string, string_pattern) +</pre></div></div></li> + +<li> +<p>Returns the first position of <tt>string_pattern</tt> within <tt>string</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might contain the pattern,</li> + +<li><tt>string_pattern</tt> : a pattern <tt>string</tt> to be matched.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the first position that <tt>string_pattern</tt> appears within <tt>string</tt>, or -1 if it does not appear,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": position("ppphonepp", "phone"), + "v2": position("hone", "phone") +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 2, "v2": -1 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="regexp_contains"></a>regexp_contains</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>regexp_contains(string, string_pattern[, string_flags]) +</pre></div></div></li> + +<li> +<p>Checks whether the strings <tt>string</tt> contains the regular expression pattern <tt>string_pattern</tt> (a Java regular expression pattern).</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might contain the pattern,</li> + +<li><tt>string_pattern</tt> : a pattern <tt>string</tt> to be matched,</li> + +<li><tt>string_flag</tt> : (Optional) a <tt>string</tt> with flags to be used during regular expression matching. + +<ul> + +<li>The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).</li> + </ul></li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>boolean</tt>, returns <tt>true</tt> if <tt>string</tt> contains the pattern <tt>string_pattern</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error,</li> + +<li><tt>false</tt> otherwise.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": regexp_contains("pphonepp", "p*hone"), + "v2": regexp_contains("hone", "p+hone") +} +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": true, "v2": false } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="regexp_like"></a>regexp_like</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>regexp_like(string, string_pattern[, string_flags]) +</pre></div></div></li> + +<li> +<p>Checks whether the string <tt>string</tt> exactly matches the regular expression pattern <tt>string_pattern</tt> (a Java regular expression pattern).</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might contain the pattern,</li> + +<li><tt>string_pattern</tt> : a pattern <tt>string</tt> that might be contained,</li> + +<li><tt>string_flag</tt> : (Optional) a <tt>string</tt> with flags to be used during regular expression matching. + +<ul> + +<li>The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).</li> + </ul></li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>boolean</tt> value, <tt>true</tt> if <tt>string</tt> contains the pattern <tt>string_pattern</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error,</li> + +<li><tt>false</tt> otherwise.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": regexp_like(" can't stand at&t the network is horrible:(", ".*at&t.*"), + "v2": regexp_like("at&t", ".*att.*") +}; +</pre></div></div></li> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": true, "v2": false } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="regexp_position"></a>regexp_position</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>regexp_position(string, string_pattern[, string_flags]) +</pre></div></div></li> + +<li> +<p>Returns first position of the regular expression <tt>string_pattern</tt> (a Java regular expression pattern) within <tt>string</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might contain the pattern,</li> + +<li><tt>string_pattern</tt> : a pattern <tt>string</tt> to be matched,</li> + +<li><tt>string_flag</tt> : (Optional) a <tt>string</tt> with flags to be used during regular expression matching. + +<ul> + +<li>The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).</li> + </ul></li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the first position that the regular expression <tt>string_pattern</tt> appears in <tt>string</tt>, or -1 if it does not appear.</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": regexp_position("pphonepp", "p*hone"), + "v2": regexp_position("hone", "p+hone") +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": 0, "v2": -1 } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="regexp_replace"></a>regexp_replace</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>regexp_replace(string, string_pattern, string_replacement[, string_flags]) +</pre></div></div></li> + +<li> +<p>Checks whether the string <tt>string</tt> matches the given regular expression pattern <tt>string_pattern</tt> (a Java regular expression pattern), and replace the matched pattern <tt>string_pattern</tt> with the new pattern <tt>string_replacement</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might contain the pattern,</li> + +<li><tt>string_pattern</tt> : a pattern <tt>string</tt> to be matched,</li> + +<li><tt>string_replacement</tt> : a pattern <tt>string</tt> to be used as the replacement,</li> + +<li><tt>string_flag</tt> : (Optional) a <tt>string</tt> with flags to be used during replace. + +<ul> + +<li>The following modes are enabled with these flags: dotall (s), multiline (m), case_insensitive (i), and comments and whitespace (x).</li> + </ul></li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>Returns a <tt>string</tt> that is obtained after the replacements,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>regexp_replace(" like iphone the voicemail_service is awesome", " like iphone", "like android") +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"like android the voicemail_service is awesome" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="repeat"></a>repeat</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>repeat(string, n) +</pre></div></div></li> + +<li> +<p>Returns a string formed by repeating the input <tt>string</tt> <tt>n</tt> times.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be repeated,</li> + +<li><tt>offset</tt> : an <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt> value as the starting offset of the substring in <tt>string</tt>.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a string that repeats the input <tt>string</tt> <tt>n</tt> times,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>a type error will be raised if: + +<ul> + +<li>the first argument is any other non-string value,</li> + +<li>or, the second argument is not a <tt>tinyint</tt>, <tt>smallint</tt>, <tt>integer</tt>, or <tt>bigint</tt>.</li> + </ul></li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>repeat("test", 3); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"testtesttest" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="rtrim"></a>rtrim</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>rtrim(string[, chars]); +</pre></div></div></li> + +<li> +<p>Returns a new string with all trailing characters that appear in <tt>chars</tt> removed. By default, white space is the character to trim.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be trimmed,</li> + +<li><tt>chars</tt> : a <tt>string</tt> that contains characters that are used to trim.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a trimmed, new <tt>string</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> +</ul> + +<ul> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1": rtrim("i like iphone", "iphone"), + "v2": rtrim("i like iphone", "oneiph") +}; +</pre></div></div></li> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": "i like ", "v2": "i like " } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="split"></a>split</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>split(string, sep) +</pre></div></div></li> + +<li> +<p>Splits the input <tt>string</tt> into an array of substrings separated by the string <tt>sep</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be split.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>an array of substrings by splitting the input <tt>string</tt> by <tt>sep</tt>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>split("test driven development", " "); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>[ "test", "driven", "development" ] +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="starts_with"></a>starts_with</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>starts_with(string, substring_to_start_with) +</pre></div></div></li> + +<li> +<p>Checks whether the string <tt>string</tt> starts with the string <tt>substring_to_start_with</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that might start with the given string.</li> + +<li><tt>substring_to_start_with</tt> : a <tt>string</tt> that might be contained as the starting substring.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>boolean</tt>, returns <tt>true</tt> if <tt>string</tt> starts with the string <tt>substring_to_start_with</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error,</li> + +<li><tt>false</tt> otherwise.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ + "v1" : starts_with(" like the plan, amazing", " like"), + "v2" : starts_with("I like the plan, amazing", " like") +}; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "v1": true, "v2": false } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="string_concat"></a>string_concat</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>string_concat(array) +</pre></div></div></li> + +<li> +<p>Concatenates an array of strings <tt>array</tt> into a single string.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>array</tt> : an <tt>array</tt> or <tt>multiset</tt> of <tt>string</tt>s (could be <tt>null</tt> or <tt>missing</tt>) to be concatenated.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the concatenated <tt>string</tt> value,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li><tt>missing</tt> if any element in the input array is <tt>missing</tt>,</li> + +<li><tt>null</tt> if any element in the input array is <tt>null</tt> but no element in the input array is <tt>missing</tt>,</li> + +<li>any other non-array input value or non-integer element in the input array will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>string_concat(["ASTERIX", " ", "ROCKS!"]); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"ASTERIX ROCKS!" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="string_join"></a>string_join</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>string_join(array, string) +</pre></div></div></li> + +<li> +<p>Joins an array or multiset of strings <tt>array</tt> with the given separator <tt>string</tt> into a single string.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>array</tt> : an <tt>array</tt> or <tt>multiset</tt> of strings (could be <tt>null</tt>) to be joined.</li> + +<li><tt>string</tt> : a <tt>string</tt> to serve as the separator.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>the joined <tt>string</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li><tt>missing</tt> if the first argument array contains a <tt>missing</tt>,</li> + +<li><tt>null</tt> if the first argument array contains a <tt>null</tt> but does not contain a <tt>missing</tt>,</li> + +<li>a type error will be raised if: + +<ul> + +<li>the first argument is any other non-array value, or contains any other non-string value,</li> + +<li>or, the second argument is any other non-string value.</li> + </ul></li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>string_join(["ASTERIX", "ROCKS~"], "!! "); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"ASTERIX!! ROCKS~" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="string_to_codepoint"></a>string_to_codepoint</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>string_to_codepoint(string) +</pre></div></div></li> + +<li> +<p>Converts the string <tt>string</tt> to its code_based representation.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> that will be converted.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>an <tt>array</tt> of the code points for the string <tt>string</tt>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>string_to_codepoint("Hello ASTERIX!"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>[ 72, 101, 108, 108, 111, 32, 65, 83, 84, 69, 82, 73, 88, 33 ] +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="codepoint_to_string"></a>codepoint_to_string</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>codepoint_to_string(array) +</pre></div></div></li> + +<li> +<p>Converts the ordered code_based representation <tt>array</tt> to the corresponding string.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>array</tt> : an <tt>array</tt> of integer code_points.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> representation of <tt>array</tt>.</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li><tt>missing</tt> if any element in the input array is <tt>missing</tt>,</li> + +<li><tt>null</tt> if any element in the input array is <tt>null</tt> but no element in the input array is <tt>missing</tt>,</li> + +<li>any other non-array input value or non-integer element in the input array will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>codepoint_to_string([72, 101, 108, 108, 111, 32, 65, 83, 84, 69, 82, 73, 88, 33]); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"Hello ASTERIX!" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="substr"></a>substr</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>substr(string, offset[, length]) +</pre></div></div></li> + +<li> +<p>Returns the substring from the given string <tt>string</tt> based on the given start offset <tt>offset</tt> with the optional <tt>length</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be extracted,</li> + +<li><tt>offset</tt> : an <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt> value as the starting offset of the substring in <tt>string</tt>,</li> + +<li><tt>length</tt> : (Optional) an an <tt>tinyint</tt>/<tt>smallint</tt>/<tt>integer</tt>/<tt>bigint</tt> value as the length of the substring.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> that represents the substring,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>a type error will be raised if: + +<ul> + +<li>the first argument is any other non-string value,</li> + +<li>or, the second argument is not a <tt>tinyint</tt>, <tt>smallint</tt>, <tt>integer</tt>, or <tt>bigint</tt>,</li> + +<li>or, the third argument is not a <tt>tinyint</tt>, <tt>smallint</tt>, <tt>integer</tt>, or <tt>bigint</tt> if the argument is present.</li> + </ul></li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>substr("test string", 6, 3); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"str" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="substring_before"></a>substring_before</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>substring_before(string, string_pattern) +</pre></div></div></li> + +<li> +<p>Returns the substring from the given string <tt>string</tt> before the given pattern <tt>string_pattern</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be extracted.</li> + +<li><tt>string_pattern</tt> : a <tt>string</tt> pattern to be searched.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> that represents the substring,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>substring_before(" like iphone", "iphone"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>" like " +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="substring_after"></a>substring_after</h3> + +<ul> + +<li> +<p>Syntax:</p> +<p>substring_after(string, string_pattern);</p></li> + +<li> +<p>Returns the substring from the given string <tt>string</tt> after the given pattern <tt>string_pattern</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be extracted.</li> + +<li><tt>string_pattern</tt> : a <tt>string</tt> pattern to be searched.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> that represents the substring,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> +</ul> + +<ul> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>substring_after(" like iphone", "iph"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"one" +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="trim"></a>trim</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>trim(string[, chars]); +</pre></div></div></li> + +<li> +<p>Returns a new string with all leading characters that appear in <tt>chars</tt> removed. By default, white space is the character to trim.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be trimmed,</li> + +<li><tt>chars</tt> : a <tt>string</tt> that contains characters that are used to trim.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a trimmed, new <tt>string</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> +</ul> + +<ul> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>trim("i like iphone", "iphoen"); +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>" like " +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="upper"></a>upper</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>upper(string) +</pre></div></div></li> + +<li> +<p>Converts a given string <tt>string</tt> to its uppercase form.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : a <tt>string</tt> to be converted.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> as the uppercase form of the given <tt>string</tt>,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>upper("hello") +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>"HELLO" +</pre></div></div></li> +</ul> +<!-- ! Licensed to the Apache Software Foundation (ASF) under one + ! or more contributor license agreements. See the NOTICE file + ! distributed with this work for additional information + ! regarding copyright ownership. The ASF licenses this file + ! to you under the Apache License, Version 2.0 (the + ! "License"); you may not use this file except in compliance + ! with the License. You may obtain a copy of the License at + ! + ! http://www.apache.org/licenses/LICENSE-2.0 + ! + ! Unless required by applicable law or agreed to in writing, + ! software distributed under the License is distributed on an + ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + ! KIND, either express or implied. See the License for the + ! specific language governing permissions and limitations + ! under the License. + ! --></div></div> +<div class="section"> +<h2><a name="Binary_Functions"></a><a name="BinaryFunctions" id="BinaryFunctions">Binary Functions</a></h2> +<div class="section"> +<h3><a name="parse_binary"></a>parse_binary</h3> + +<ul> + +<li> +<p>Syntax:</p> +<p>parse_binary(string, encoding)</p></li> + +<li> +<p>Creates a <tt>binary</tt> from an string encoded in <tt>encoding</tt> format.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>string</tt> : an encoded <tt>string</tt>,</li> + +<li><tt>encoding</tt> : a string notation specifies the encoding type of the given <tt>string</tt>. Currently we support <tt>hex</tt> and <tt>base64</tt> format.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>binary</tt> that is decoded from the given <tt>string</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> +<p>[ parse_binary(“ABCDEF0123456789”,“hex”), parse_binary(“abcdef0123456789”,“HEX”), parse_binary(‘QXN0ZXJpeAE=’,“base64”) ];</p></li> + +<li> +<p>The expected result is:</p> +<p>[ hex(“ABCDEF0123456789”), hex(“ABCDEF0123456789”), hex(“4173746572697801”) ]</p></li> +</ul></div> +<div class="section"> +<h3><a name="print_binary"></a>print_binary</h3> + +<ul> + +<li> +<p>Syntax:</p> +<p>print_binary(binary, encoding)</p></li> + +<li> +<p>Prints a <tt>binary</tt> to the required encoding <tt>string</tt> format.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>binary</tt> : a <tt>binary</tt> data need to be printed.</li> + +<li><tt>encoding</tt> : a string notation specifies the expected encoding type. Currently we support <tt>hex</tt> and <tt>base64</tt> format.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>string</tt> that represents the encoded format of a <tt>binary</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-string input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>[ print_binary(hex("ABCDEF0123456789"), "base64"), print_binary(base64("q83vASNFZ4k="), "hex") ] +</pre></div></div></li> + +<li> +<p>The expected result are:</p> + +<div class="source"> +<div class="source"> +<pre>[ "q83vASNFZ4k=", "ABCDEF0123456789" ] +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="binary_length"></a>binary_length</h3> + +<ul> + +<li> +<p>Syntax:</p> +<p>binary_length(binary)</p></li> + +<li> +<p>Returns the number of bytes storing the binary data.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>binary</tt> : a <tt>binary</tt> value to be checked.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>an <tt>bigint</tt> that represents the number of bytes,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li>any other non-binary input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>binary_length(hex("00AA")) +</pre></div></div></li> + +<li> +<p>The expected result is:</p> +<p>2</p></li> +</ul></div> +<div class="section"> +<h3><a name="sub_binary"></a>sub_binary</h3> + +<ul> + +<li> +<p>Syntax:</p> +<p>sub_binary(binary, offset[, length])</p></li> + +<li> +<p>Returns the sub binary from the given <tt>binary</tt> based on the given start offset with the optional <tt>length</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>binary</tt> : a <tt>binary</tt> to be extracted,</li> + +<li><tt>offset</tt> : a <tt>tinyint</tt>, <tt>smallint</tt>, <tt>integer</tt>, or <tt>bigint</tt> value as the starting offset of the sub binary in <tt>binary</tt>,</li> + +<li><tt>length</tt> : (Optional) a <tt>tinyint</tt>, <tt>smallint</tt>, <tt>integer</tt>, or <tt>bigint</tt> value as the length of the sub binary.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a <tt>binary</tt> that represents the sub binary,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>a type error will be raised if: + +<ul> + +<li>the first argument is any other non-binary value,</li> + +<li>or, the second argument is any other non-integer value,</li> + +<li>or, the third argument is any other non-integer value, if it is present.</li> + </ul></li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>sub_binary(hex("AABBCCDD"), 4); +</pre></div></div></li> + +<li> +<p>The expected result is</p> + +<div class="source"> +<div class="source"> +<pre>hex("DD") +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="binary_concat"></a>binary_concat</h3> + +<ul> + +<li> +<p>Syntax:</p> +<p>binary_concat(array)</p></li> + +<li> +<p>Concatenates a binary <tt>array</tt> or <tt>multiset</tt> into a single binary.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>array</tt> : an <tt>array</tt> or <tt>multiset</tt> of binaries (could be <tt>null</tt> or <tt>missing</tt>) to be concatenated.</li> + </ul></li> + +<li>Return Value : + +<ul> + +<li>the concatenated <tt>binary</tt> value,</li> + +<li><tt>missing</tt> if the argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if the argument is a <tt>null</tt> value,</li> + +<li><tt>missing</tt> if any element in the input array is <tt>missing</tt>,</li> + +<li><tt>null</tt> if any element in the input array is <tt>null</tt> but no element in the input array is <tt>missing</tt>,</li> + +<li>any other non-array input value or non-binary element in the input array will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> +<p>binary_concat([hex(“42”), hex(""), hex(‘42’)]);</p></li> + +<li> +<p>The expected result is</p> +<p>hex(“4242”)</p></li> +</ul> +<!-- ! Licensed to the Apache Software Foundation (ASF) under one + ! or more contributor license agreements. See the NOTICE file + ! distributed with this work for additional information + ! regarding copyright ownership. The ASF licenses this file + ! to you under the Apache License, Version 2.0 (the + ! "License"); you may not use this file except in compliance + ! with the License. You may obtain a copy of the License at + ! + ! http://www.apache.org/licenses/LICENSE-2.0 + ! + ! Unless required by applicable law or agreed to in writing, + ! software distributed under the License is distributed on an + ! "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + ! KIND, either express or implied. See the License for the + ! specific language governing permissions and limitations + ! under the License. + ! --></div></div> +<div class="section"> +<h2><a name="Spatial_Functions"></a><a name="SpatialFunctions" id="SpatialFunctions">Spatial Functions</a></h2> +<div class="section"> +<h3><a name="create_point"></a>create_point</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>create_point(x, y) +</pre></div></div></li> + +<li> +<p>Creates the primitive type <tt>point</tt> using an <tt>x</tt> and <tt>y</tt> value.</p></li> + +<li>Arguments:</li> + +<li><tt>x</tt> : a <tt>double</tt> that represents the x-coordinate,</li> + +<li><tt>y</tt> : a <tt>double</tt> that represents the y-coordinate.</li> + +<li>Return Value:</li> + +<li>a <tt>point</tt> representing the ordered pair (<tt>x</tt>, <tt>y</tt>),</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-double input value will cause a type error.</li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "point": create_point(30.0,70.0) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "point": point("30.0,70.0") } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="create_line"></a>create_line</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>create_line(point1, point2) +</pre></div></div></li> + +<li> +<p>Creates the primitive type <tt>line</tt> using <tt>point1</tt> and <tt>point2</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>point1</tt> : a <tt>point</tt> that represents the start point of the line.</li> + +<li><tt>point2</tt> : a <tt>point</tt> that represents the end point of the line.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a spatial <tt>line</tt> created using the points provided in <tt>point1</tt> and <tt>point2</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-point input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "line": create_line(create_point(30.0,70.0), create_point(50.0,90.0)) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "line": line("30.0,70.0 50.0,90.0") } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="create_rectangle"></a>create_rectangle</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>create_rectangle(point1, point2) +</pre></div></div></li> + +<li> +<p>Creates the primitive type <tt>rectangle</tt> using <tt>point1</tt> and <tt>point2</tt>.</p></li> + +<li>Arguments: + +<ul> + +<li><tt>point1</tt> : a <tt>point</tt> that represents the lower_left point of the rectangle.</li> + +<li><tt>point2</tt> : a <tt>point</tt> that represents the upper_right point of the rectangle.</li> + </ul></li> + +<li>Return Value: + +<ul> + +<li>a spatial <tt>rectangle</tt> created using the points provided in <tt>point1</tt> and <tt>point2</tt>,</li> + +<li><tt>missing</tt> if any argument is a <tt>missing</tt> value,</li> + +<li><tt>null</tt> if any argument is a <tt>null</tt> value but no argument is a <tt>missing</tt> value,</li> + +<li>any other non-point input value will cause a type error.</li> + </ul></li> + +<li> +<p>Example:</p> + +<div class="source"> +<div class="source"> +<pre>{ "rectangle": create_rectangle(create_point(30.0,70.0), create_point(50.0,90.0)) }; +</pre></div></div></li> +</ul> + +<ul> + +<li> +<p>The expected result is:</p> + +<div class="source"> +<div class="source"> +<pre>{ "rectangle": rectangle("30.0,70.0 50.0,90.0") } +</pre></div></div></li> +</ul></div> +<div class="section"> +<h3><a name="create_circle"></a>create_circle</h3> + +<ul> + +<li> +<p>Syntax:</p> + +<div class="source"> +<div class="source"> +<pre>create_circle(point, radius) +</pre></div></div></li> + +<li> +<p>Creates the primitive type <tt>circl
<TRUNCATED>
