Modified: phoenix/site/publish/language/functions.html URL: http://svn.apache.org/viewvc/phoenix/site/publish/language/functions.html?rev=1739811&r1=1739810&r2=1739811&view=diff ============================================================================== --- phoenix/site/publish/language/functions.html (original) +++ phoenix/site/publish/language/functions.html Mon Apr 18 18:55:29 2016 @@ -1,7 +1,7 @@ <!DOCTYPE html> <!-- - Generated by Apache Maven Doxia at 2016-03-10 + Generated by Apache Maven Doxia at 2016-04-18 Rendered using Reflow Maven Skin 1.1.0 (http://andriusvelykis.github.io/reflow-maven-skin) --> <html xml:lang="en" lang="en"> @@ -62,12 +62,12 @@ <li ><a href="../issues.html" title="Issue Tracking">Issue Tracking</a></li> <li ><a href="../download.html" title="Download">Download</a></li> <li ><a href="../installation.html" title="Installation">Installation</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="../contributing.html" title="How to Contribute">How to Contribute</a></li> <li ><a href="../develop.html" title="How to Develop">How to Develop</a></li> <li ><a href="../building_website.html" title="How to Update Website">How to Update Website</a></li> <li ><a href="../release.html" title="How to Release">How to Release</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="http://www.apache.org/licenses/"; title="License" class="externalLink">License</a></li> </ul> </li> @@ -81,7 +81,7 @@ <li ><a href="../upgrading.html" title="Backward Compatibility">Backward Compatibility</a></li> <li ><a href="../release_notes.html" title="Release Notes">Release Notes</a></li> <li ><a href="../pherf.html" title="Performance Testing">Performance Testing</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="../phoenix_spark.html" title="Apache Spark Integration">Apache Spark Integration</a></li> <li ><a href="../pig_integration.html" title="Apache Pig Integration">Apache Pig Integration</a></li> <li ><a href="../phoenix_mr.html" title="Map Reduce Integration">Map Reduce Integration</a></li> @@ -93,31 +93,31 @@ <ul class="dropdown-menu"> <li ><a href="../transactions.html" title="Transactions">Transactions</a></li> <li ><a href="../udf.html" title="User-defined Functions">User-defined Functions</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="../secondary_indexing.html" title="Secondary Indexes">Secondary Indexes</a></li> <li ><a href="../update_statistics.html" title="Statistics Collection">Statistics Collection</a></li> <li ><a href="../rowtimestamp.html" title="Row Timestamp Column">Row Timestamp Column</a></li> <li ><a href="../salted.html" title="Salted Tables">Salted Tables</a></li> <li ><a href="../skip_scan.html" title="Skip Scan">Skip Scan</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="../views.html" title="Views">Views</a></li> <li ><a href="../multi-tenancy.html" title="Multi tenancy">Multi tenancy</a></li> <li ><a href="../dynamic_columns.html" title="Dynamic Columns">Dynamic Columns</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="../bulk_dataload.html" title="Bulk Loading">Bulk Loading</a></li> <li ><a href="../server.html" title="Query Server">Query Server</a></li> <li ><a href="../tracing.html" title="Tracing">Tracing</a></li> <li ><a href="../metrics.html" title="Metrics">Metrics</a></li> </ul> </li> - <li class="dropdown"> + <li class="dropdown active"> <a href="#" class="dropdown-toggle" data-toggle="dropdown">Reference <b class="caret"></b></a> <ul class="dropdown-menu"> <li ><a href="index.html" title="Grammar">Grammar</a></li> <li class="active"><a href="" title="Functions">Functions</a></li> <li ><a href="datatypes.html" title="Datatypes">Datatypes</a></li> <li ><a href="../array_type.html" title="ARRAY type">ARRAY type</a></li> - <li class="divider"/> + <li ><a href="http:divider" title=""></a></li> <li ><a href="../paged.html" title="Row Value Constructors">Row Value Constructors</a></li> <li ><a href="../sequences.html" title="Sequences">Sequences</a></li> <li ><a href="../joins.html" title="Joins">Joins</a></li> @@ -144,1622 +144,7 @@ <div class="body-content"> <div class="page-header"> <h1>Functions</h1> -</div> - -<!-- } --> -<h3 id="functions">Aggregate Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#avg" >AVG</a><br /> - - <a href="#count" >COUNT</a><br /> - - <a href="#max" >MAX</a><br /> - - <a href="#min" >MIN</a><br /> - - <a href="#sum" >SUM</a><br /> - - <a href="#percentile_cont" >PERCENTILE_CONT</a><br /> - - <a href="#percentile_disc" >PERCENTILE_DISC</a><br /> - - <a href="#percent_rank" >PERCENT_RANK</a><br /> - - <a href="#first_value" >FIRST_VALUE</a><br /> - - <a href="#last_value" >LAST_VALUE</a><br /> - - <a href="#nth_value" >NTH_VALUE</a><br /> - - <a href="#stddev_pop" >STDDEV_POP</a><br /> - - <a href="#stddev_samp" >STDDEV_SAMP</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#avg" >AVG</a><br /> - - <a href="#count" >COUNT</a><br /> - - <a href="#max" >MAX</a><br /> - - <a href="#min" >MIN</a><br /> - - <a href="#sum" >SUM</a><br /> - </td><td class="index"> - - <a href="#percentile_cont" >PERCENTILE_CONT</a><br /> - - <a href="#percentile_disc" >PERCENTILE_DISC</a><br /> - - <a href="#percent_rank" >PERCENT_RANK</a><br /> - - <a href="#first_value" >FIRST_VALUE</a><br /> - - <a href="#last_value" >LAST_VALUE</a><br /> - </td><td class="index"> - - <a href="#nth_value" >NTH_VALUE</a><br /> - - <a href="#stddev_pop" >STDDEV_POP</a><br /> - - <a href="#stddev_samp" >STDDEV_SAMP</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - -<h3>String Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#substr" >SUBSTR</a><br /> - - <a href="#instr" >INSTR</a><br /> - - <a href="#trim" >TRIM</a><br /> - - <a href="#ltrim" >LTRIM</a><br /> - - <a href="#rtrim" >RTRIM</a><br /> - - <a href="#lpad" >LPAD</a><br /> - - <a href="#length" >LENGTH</a><br /> - - <a href="#regexp_substr" >REGEXP_SUBSTR</a><br /> - - <a href="#regexp_replace" >REGEXP_REPLACE</a><br /> - - <a href="#regexp_split" >REGEXP_SPLIT</a><br /> - - <a href="#upper" >UPPER</a><br /> - - <a href="#lower" >LOWER</a><br /> - - <a href="#reverse" >REVERSE</a><br /> - - <a href="#to_char" >TO_CHAR</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#substr" >SUBSTR</a><br /> - - <a href="#instr" >INSTR</a><br /> - - <a href="#trim" >TRIM</a><br /> - - <a href="#ltrim" >LTRIM</a><br /> - - <a href="#rtrim" >RTRIM</a><br /> - </td><td class="index"> - - <a href="#lpad" >LPAD</a><br /> - - <a href="#length" >LENGTH</a><br /> - - <a href="#regexp_substr" >REGEXP_SUBSTR</a><br /> - - <a href="#regexp_replace" >REGEXP_REPLACE</a><br /> - - <a href="#regexp_split" >REGEXP_SPLIT</a><br /> - </td><td class="index"> - - <a href="#upper" >UPPER</a><br /> - - <a href="#lower" >LOWER</a><br /> - - <a href="#reverse" >REVERSE</a><br /> - - <a href="#to_char" >TO_CHAR</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - -<h3>Time and Date Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#to_date" >TO_DATE</a><br /> - - <a href="#current_date" >CURRENT_DATE</a><br /> - - <a href="#current_time" >CURRENT_TIME</a><br /> - - <a href="#convert_tz" >CONVERT_TZ</a><br /> - - <a href="#timezone_offset" >TIMEZONE_OFFSET</a><br /> - - <a href="#now" >NOW</a><br /> - - <a href="#year" >YEAR</a><br /> - - <a href="#month" >MONTH</a><br /> - - <a href="#week" >WEEK</a><br /> - - <a href="#dayofmonth" >DAYOFMONTH</a><br /> - - <a href="#hour" >HOUR</a><br /> - - <a href="#minute" >MINUTE</a><br /> - - <a href="#second" >SECOND</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#to_date" >TO_DATE</a><br /> - - <a href="#current_date" >CURRENT_DATE</a><br /> - - <a href="#current_time" >CURRENT_TIME</a><br /> - - <a href="#convert_tz" >CONVERT_TZ</a><br /> - - <a href="#timezone_offset" >TIMEZONE_OFFSET</a><br /> - </td><td class="index"> - - <a href="#now" >NOW</a><br /> - - <a href="#year" >YEAR</a><br /> - - <a href="#month" >MONTH</a><br /> - - <a href="#week" >WEEK</a><br /> - - <a href="#dayofmonth" >DAYOFMONTH</a><br /> - </td><td class="index"> - - <a href="#hour" >HOUR</a><br /> - - <a href="#minute" >MINUTE</a><br /> - - <a href="#second" >SECOND</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - -<h3>Numeric Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#round" >ROUND</a><br /> - - <a href="#ceil" >CEIL</a><br /> - - <a href="#floor" >FLOOR</a><br /> - - <a href="#trunc" >TRUNC</a><br /> - - <a href="#to_number" >TO_NUMBER</a><br /> - - <a href="#rand" >RAND</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#round" >ROUND</a><br /> - - <a href="#ceil" >CEIL</a><br /> - </td><td class="index"> - - <a href="#floor" >FLOOR</a><br /> - - <a href="#trunc" >TRUNC</a><br /> - </td><td class="index"> - - <a href="#to_number" >TO_NUMBER</a><br /> - - <a href="#rand" >RAND</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - -<h3>Array Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#array_elem" >ARRAY_ELEM</a><br /> - - <a href="#array_length" >ARRAY_LENGTH</a><br /> - - <a href="#array_append" >ARRAY_APPEND</a><br /> - - <a href="#array_prepend" >ARRAY_PREPEND</a><br /> - - <a href="#array_cat" >ARRAY_CAT</a><br /> - - <a href="#array_fill" >ARRAY_FILL</a><br /> - - <a href="#array_to_string" >ARRAY_TO_STRING</a><br /> - - <a href="#any" >ANY</a><br /> - - <a href="#all" >ALL</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#array_elem" >ARRAY_ELEM</a><br /> - - <a href="#array_length" >ARRAY_LENGTH</a><br /> - - <a href="#array_append" >ARRAY_APPEND</a><br /> - </td><td class="index"> - - <a href="#array_prepend" >ARRAY_PREPEND</a><br /> - - <a href="#array_cat" >ARRAY_CAT</a><br /> - - <a href="#array_fill" >ARRAY_FILL</a><br /> - </td><td class="index"> - - <a href="#array_to_string" >ARRAY_TO_STRING</a><br /> - - <a href="#any" >ANY</a><br /> - - <a href="#all" >ALL</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - -<h3>Math Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#sign" >SIGN</a><br /> - - <a href="#abs" >ABS</a><br /> - - <a href="#sqrt" >SQRT</a><br /> - - <a href="#cbrt" >CBRT</a><br /> - - <a href="#exp" >EXP</a><br /> - - <a href="#power" >POWER</a><br /> - - <a href="#ln" >LN</a><br /> - - <a href="#log" >LOG</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#sign" >SIGN</a><br /> - - <a href="#abs" >ABS</a><br /> - - <a href="#sqrt" >SQRT</a><br /> - </td><td class="index"> - - <a href="#cbrt" >CBRT</a><br /> - - <a href="#exp" >EXP</a><br /> - - <a href="#power" >POWER</a><br /> - </td><td class="index"> - - <a href="#ln" >LN</a><br /> - - <a href="#log" >LOG</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - -<h3>Other Functions</h3> -<!-- syntax-start -<p class="notranslate"> - - <a href="#md5" >MD5</a><br /> - - <a href="#invert" >INVERT</a><br /> - - <a href="#encode" >ENCODE</a><br /> - - <a href="#decode" >DECODE</a><br /> - - <a href="#coalesce" >COALESCE</a><br /> -</p> -syntax-end --> -<!-- railroad-start --> -<table class="notranslate index"> - <tr> - <td class="index"> - - <a href="#md5" >MD5</a><br /> - - <a href="#invert" >INVERT</a><br /> - </td><td class="index"> - - <a href="#encode" >ENCODE</a><br /> - - <a href="#decode" >DECODE</a><br /> - </td><td class="index"> - - <a href="#coalesce" >COALESCE</a><br /> - </td> - </tr> -</table> -<!-- railroad-end --> - - - -<h3 id="avg" class="notranslate">AVG</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -AVG ( { <a href="index.html#term">numericTerm</a> } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">AVG ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -AVG ( { <a href="index.html#term">numericTerm</a> } ) -</pre> -syntax-end --> -<p>The average (mean) value. If no rows are selected, the result is <code>NULL</code>. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.</p> -<p>Example:</p> -<p class="notranslate">AVG(X)</p> - -<h3 id="count" class="notranslate">COUNT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -COUNT( [ DISTINCT ] { * | { <a href="index.html#term">term</a> } } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">COUNT (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DISTINCT</code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">*</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">term</a></code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -COUNT( [ DISTINCT ] { * | { <a href="index.html#term">term</a> } } ) -</pre> -syntax-end --> -<p>The count of all row, or of the non-null values. This method returns a long. When <code>DISTINCT</code> is used, it counts only distinct values. If no rows are selected, the result is 0. Aggregates are only allowed in select statements.</p> -<p>Example:</p> -<p class="notranslate">COUNT(*)</p> - -<h3 id="max" class="notranslate">MAX</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -MAX(<a href="index.html#term">term</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">MAX ( <a href="index.html#term">term</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -MAX(<a href="index.html#term">term</a>) -</pre> -syntax-end --> -<p>The highest value. If no rows are selected, the result is <code>NULL</code>. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.</p> -<p>Example:</p> -<p class="notranslate">MAX(NAME)</p> - -<h3 id="min" class="notranslate">MIN</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -MIN(<a href="index.html#term">term</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">MIN ( <a href="index.html#term">term</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -MIN(<a href="index.html#term">term</a>) -</pre> -syntax-end --> -<p>The lowest value. If no rows are selected, the result is <code>NULL</code>. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.</p> -<p>Example:</p> -<p class="notranslate">MIN(NAME)</p> - -<h3 id="sum" class="notranslate">SUM</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -SUM( { <a href="index.html#term">numericTerm</a> } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SUM ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -SUM( { <a href="index.html#term">numericTerm</a> } ) -</pre> -syntax-end --> -<p>The sum of all values. If no rows are selected, the result is <code>NULL</code>. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.</p> -<p>Example:</p> -<p class="notranslate">SUM(X)</p> - -<h3 id="percentile_cont" class="notranslate">PERCENTILE_CONT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -PERCENTILE_CONT( { <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#term">numericTerm</a> } { ASC | DESC } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">PERCENTILE_CONT ( <a href="index.html#numeric">numeric</a> ) WITHIN GROUP ( ORDER BY <a href="index.html#term">numericTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -PERCENTILE_CONT( { <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#term">numericTerm</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>The nth percentile of values in the column. The percentile value can be between 0 and 1 inclusive. Aggregates are only allowed in select statements. The returned value is of decimal data type.</p> -<p>Example:</p> -<p class="notranslate">PERCENTILE_CONT( 0.9 ) WITHIN GROUP (ORDER BY X ASC)</p> - -<h3 id="percentile_disc" class="notranslate">PERCENTILE_DISC</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -PERCENTILE_DIST( { <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#term">numericTerm</a> } { ASC | DESC } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">PERCENTILE_DIST ( <a href="index.html#numeric">numeric</a> ) WITHIN GROUP ( ORDER BY <a href="index.html#term">numericTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -PERCENTILE_DIST( { <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#term">numericTerm</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p><code>PERCENTILE_DISC</code> is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.</p> -<p>Example:</p> -<p class="notranslate">PERCENTILE_DISC( 0.9 ) WITHIN GROUP (ORDER BY X DESC)</p> - -<h3 id="percent_rank" class="notranslate">PERCENT_RANK</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -PERCENT_RANK( { <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#term">numericTerm</a> } { ASC | DESC } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">PERCENT_RANK ( <a href="index.html#numeric">numeric</a> ) WITHIN GROUP ( ORDER BY <a href="index.html#term">numericTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -PERCENT_RANK( { <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#term">numericTerm</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>The percentile rank for a hypothetical value, if inserted into the column. Aggregates are only allowed in select statements. The returned value is of decimal data type.</p> -<p>Example:</p> -<p class="notranslate">PERCENT_RANK( 100 ) WITHIN GROUP (ORDER BY X ASC)</p> - -<h3 id="first_value" class="notranslate">FIRST_VALUE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -FIRST_VALUE( { <a href="index.html#expression">expression</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">FIRST_VALUE ( <a href="index.html#expression">expression</a> ) WITHIN GROUP ( ORDER BY <a href="index.html#expression">expression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -FIRST_VALUE( { <a href="index.html#expression">expression</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>The first value in each distinct group ordered according to the <code>ORDER BY</code> specification.</p> -<p>Example:</p> -<p class="notranslate">FIRST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)</p> - -<h3 id="last_value" class="notranslate">LAST_VALUE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LAST_VALUE( { <a href="index.html#expression">expression</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LAST_VALUE ( <a href="index.html#expression">expression</a> ) WITHIN GROUP ( ORDER BY <a href="index.html#expression">expression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LAST_VALUE( { <a href="index.html#expression">expression</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>The last value in each distinct group ordered according to the <code>ORDER BY</code> specification.</p> -<p>Example:</p> -<p class="notranslate">LAST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)</p> - -<h3 id="nth_value" class="notranslate">NTH_VALUE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -NTH_VALUE( { <a href="index.html#expression">expression</a>, <a href="index.html#numeric">nthNumeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">NTH_VALUE ( <a href="index.html#expression">expression</a> , <a href="index.html#numeric">nthNumeric</a> ) WITHIN GROUP ( ORDER BY <a href="index.html#expression">expression</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c">ASC</code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c">DESC</code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -NTH_VALUE( { <a href="index.html#expression">expression</a>, <a href="index.html#numeric">nthNumeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>The nth value in each distinct group ordered according to the <code>ORDER BY</code> specification.</p> -<p>Example:</p> -<p class="notranslate">NTH_VALUE( name, 2 ) WITHIN GROUP (ORDER BY salary DESC)</p> - -<h3 id="stddev_pop" class="notranslate">STDDEV_POP</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -STDDEV_POP( { <a href="index.html#term">numericTerm</a> } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">STDDEV_POP ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -STDDEV_POP( { <a href="index.html#term">numericTerm</a> } ) -</pre> -syntax-end --> -<p>The population standard deviation of all values. Aggregates are only allowed in select statements. The returned value is of decimal data type.</p> -<p>Example:</p> -<p class="notranslate">STDDEV_POP( X )</p> - -<h3 id="stddev_samp" class="notranslate">STDDEV_SAMP</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -STDDEV_SAMP( { <a href="index.html#term">numericTerm</a> } ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">STDDEV_SAMP ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -STDDEV_SAMP( { <a href="index.html#term">numericTerm</a> } ) -</pre> -syntax-end --> -<p>The sample standard deviation of all values. Aggregates are only allowed in select statements. The returned value is of decimal data type.</p> -<p>Example:</p> -<p class="notranslate">STDDEV_SAMP( X )</p> - -<h3 id="array_elem" class="notranslate">ARRAY_ELEM</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_ELEM( <a href="index.html#term">arrayTerm</a>, <a href="index.html#term">numericTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_ELEM ( <a href="index.html#term">arrayTerm</a> , <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_ELEM( <a href="index.html#term">arrayTerm</a>, <a href="index.html#term">numericTerm</a> ) -</pre> -syntax-end --> -<p>Alternative to using array subscript notation to access an array element. Returns the element in the array at the given position. The position is one-based.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_ELEM(my_array_col, 5)<br />ARRAY_ELEM(ARRAY[1,2,3], 1)</p> - -<h3 id="array_length" class="notranslate">ARRAY_LENGTH</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_LENGTH( <a href="index.html#term">arrayTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_LENGTH ( <a href="index.html#term">arrayTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_LENGTH( <a href="index.html#term">arrayTerm</a> ) -</pre> -syntax-end --> -<p>Returns the current length of the array.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_LENGTH(my_array_col)<br />ARRAY_LENGTH(ARRAY[1,2,3])</p> - -<h3 id="array_append" class="notranslate">ARRAY_APPEND</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_APPEND( <a href="index.html#term">arrayTerm</a>, <a href="index.html#term">elementTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_APPEND ( <a href="index.html#term">arrayTerm</a> , <a href="index.html#term">elementTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_APPEND( <a href="index.html#term">arrayTerm</a>, <a href="index.html#term">elementTerm</a> ) -</pre> -syntax-end --> -<p>Appends the given element to the end of the array.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_APPEND(my_array_col, my_element_col)<br />ARRAY_APPEND(ARRAY[1,2,3], 4) evaluates to ARRAY[1,2,3,4]</p> - -<h3 id="array_prepend" class="notranslate">ARRAY_PREPEND</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_PREPEND(<a href="index.html#term">elementTerm</a>, <a href="index.html#term">arrayTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_PREPEND ( <a href="index.html#term">elementTerm</a> , <a href="index.html#term">arrayTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_PREPEND(<a href="index.html#term">elementTerm</a>, <a href="index.html#term">arrayTerm</a>) -</pre> -syntax-end --> -<p>Appends the given element to the beginning of the array.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_PREPEND(my_element_col, my_array_col)<br />ARRAY_PREPEND(0, ARRAY[1,2,3]) evaluates to ARRAY[0,1,2,3]</p> - -<h3 id="array_cat" class="notranslate">ARRAY_CAT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_CAT(<a href="index.html#term">arrayTerm</a>, <a href="index.html#term">arrayTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_CAT ( <a href="index.html#term">arrayTerm</a> , <a href="index.html#term">arrayTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_CAT(<a href="index.html#term">arrayTerm</a>, <a href="index.html#term">arrayTerm</a>) -</pre> -syntax-end --> -<p>Concatenates the input arrays and returns the result.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_CAT(my_array_col1, my_array_col2)<br />ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]) evaluates to ARRAY[1,2,3,4]</p> - -<h3 id="array_fill" class="notranslate">ARRAY_FILL</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_FILL(<a href="index.html#term">arrayTerm</a>, <a href="index.html#numeric">lengthNumeric</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_FILL ( <a href="index.html#term">arrayTerm</a> , <a href="index.html#numeric">lengthNumeric</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_FILL(<a href="index.html#term">arrayTerm</a>, <a href="index.html#numeric">lengthNumeric</a>) -</pre> -syntax-end --> -<p>Returns an array initialized with supplied value and length.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_FILL(my_element_col, my_length_col)<br />ARRAY_FILL(1, 3) evaluates to ARRAY[1,1,1]</p> - -<h3 id="array_to_string" class="notranslate">ARRAY_TO_STRING</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ARRAY_TO_STRING(<a href="index.html#term">arrayTerm</a>, <a href="index.html#string">delimiterString</a> [, <a href="index.html#string">nullString</a>]) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ARRAY_TO_STRING ( <a href="index.html#term">arrayTerm</a> , <a href="index.html#string">delimiterString</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#string">nullString</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ARRAY_TO_STRING(<a href="index.html#term">arrayTerm</a>, <a href="index.html#string">delimiterString</a> [, <a href="index.html#string">nullString</a>]) -</pre> -syntax-end --> -<p>Concatenates array elements using supplied delimiter and optional null string and returns the resulting string. If the nullString parameter is omitted or <code>NULL</code>, any null elements in the array are simply skipped and not represented in the output string.</p> -<p>Example:</p> -<p class="notranslate">ARRAY_TO_STRING(my_array_col, my_delimiter_col, my_null_string_col)<br />ARRAY_TO_STRING(ARRAY['a','b','c'], ',') evaluates to 'a,b,c'<br />ARRAY_TO_STRING(ARRAY['a','b',null,'c'], ',') evaluates to 'a,b,c'<br />ARRAY_TO_STRING(ARRAY['a','b',null,'c'], ',', 'NULL') evaluates to 'a,b,NULL,c'</p> - -<h3 id="any" class="notranslate">ANY</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ANY( <a href="index.html#term">arrayTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ANY ( <a href="index.html#term">arrayTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ANY( <a href="index.html#term">arrayTerm</a> ) -</pre> -syntax-end --> -<p>Used on the right-hand side of a comparison expression to test that any array element satisfies the comparison expression against the left-hand side.</p> -<p>Example:</p> -<p class="notranslate">1 = ANY(my_array)<br />10 > ANY(my_array)</p> - -<h3 id="all" class="notranslate">ALL</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ALL( <a href="index.html#term">arrayTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ALL ( <a href="index.html#term">arrayTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ALL( <a href="index.html#term">arrayTerm</a> ) -</pre> -syntax-end --> -<p>Used on the right-hand side of a comparison expression to test that all array elements satisfy the comparison expression against the left-hand side. of the array.</p> -<p>Example:</p> -<p class="notranslate">1 = ALL(my_array)<br />10 > ALL(my_array)</p> - -<h3 id="md5" class="notranslate">MD5</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -MD5( <a href="index.html#term">term</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">MD5 ( <a href="index.html#term">term</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -MD5( <a href="index.html#term">term</a> ) -</pre> -syntax-end --> -<p>Computes the <code>MD5</code> hash of the argument, returning the result as a <code>BINARY</code>(16).</p> -<p>Example:</p> -<p class="notranslate">MD5(my_column)</p> - -<h3 id="invert" class="notranslate">INVERT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -INVERT( <a href="index.html#term">term</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">INVERT ( <a href="index.html#term">term</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -INVERT( <a href="index.html#term">term</a> ) -</pre> -syntax-end --> -<p>Inverts the bits of the argument. The return type will be the same as the argument.</p> -<p>Example:</p> -<p class="notranslate">INVERT(my_column)</p> - -<h3 id="encode" class="notranslate">ENCODE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ENCODE( <a href="index.html#expression">expression</a>, 'BASE62' ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ENCODE ( <a href="index.html#expression">expression</a> , ' BASE62 ' )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ENCODE( <a href="index.html#expression">expression</a>, 'BASE62' ) -</pre> -syntax-end --> -<p>Encodes the expression according to the encoding format provided and returns the resulting string. For '<code>BASE62</code>', converts the given base 10 number to a base 62 number and returns a string representing the number.</p> -<p>Example:</p> -<p class="notranslate">ENCODE(myNumber, 'BASE62')</p> - -<h3 id="decode" class="notranslate">DECODE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -DECODE( <a href="index.html#expression">expression</a>, 'HEX' ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">DECODE ( <a href="index.html#expression">expression</a> , ' HEX ' )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -DECODE( <a href="index.html#expression">expression</a>, 'HEX' ) -</pre> -syntax-end --> -<p>Decodes the expression according to the encoding format provided and returns the resulting value as a <code>VARBINARY</code>. For '<code>HEX</code>', converts the hex string expression to its binary representation, providing a mechanism for inputting binary data through the console.</p> -<p>Example:</p> -<p class="notranslate">DECODE('000000008512af277ffffff8', 'HEX')</p> - -<h3 id="coalesce" class="notranslate">COALESCE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -COALESCE( <a href="index.html#term">firstTerm</a>, <a href="index.html#term">secondTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">COALESCE ( <a href="index.html#term">firstTerm</a> , <a href="index.html#term">secondTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -COALESCE( <a href="index.html#term">firstTerm</a>, <a href="index.html#term">secondTerm</a> ) -</pre> -syntax-end --> -<p>Returns the value of the first argument if not null and the second argument otherwise. Useful to guarantee that a column in an <code>UPSERT SELECT</code> command will evaluate to a non null value.</p> -<p>Example:</p> -<p class="notranslate">COALESCE(last_update_date, CURRENT_DATE())</p> - -<h3 id="sign" class="notranslate">SIGN</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -SIGN(<a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SIGN ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -SIGN(<a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns the signum function of the given numeric expression as an <code>INTEGER</code>. The return value is -1 if the given numeric expression is negative; 0 if the given numeric expression is zero; and 1 if the given numeric expression is positive.</p> -<p>Example:</p> -<p class="notranslate">SIGN(number)<br />SIGN(1.1)<br />SIGN(-1)</p> - -<h3 id="abs" class="notranslate">ABS</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ABS(<a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ABS ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ABS(<a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns the absolute value of the given numeric expression maintaining the same type.</p> -<p>Example:</p> -<p class="notranslate">ABS(number)<br />ABS(1.1)<br />ABS(-1)</p> - -<h3 id="sqrt" class="notranslate">SQRT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -SQRT(<a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SQRT ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -SQRT(<a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns the correctly rounded square root of the given non-negative numeric expression as a <code>DOUBLE</code>.</p> -<p>Example:</p> -<p class="notranslate">SQRT(number)<br />SQRT(1.1)</p> - -<h3 id="cbrt" class="notranslate">CBRT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -CBRT(<a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CBRT ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -CBRT(<a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns the cube root of the given numeric expression as a <code>DOUBLE</code>.</p> -<p>Example:</p> -<p class="notranslate">CBRT(number)<br />CBRT(1.1)<br />CBRT(-1)</p> - -<h3 id="exp" class="notranslate">EXP</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -EXP(<a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">EXP ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -EXP(<a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns Euler's number e raised to the power of the given numeric value as a <code>DOUBLE</code>.</p> -<p>Example:</p> -<p class="notranslate">EXP(number)<br />EXP(1.1)<br />EXP(-1)</p> - -<h3 id="power" class="notranslate">POWER</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -POWER(<a href="index.html#term">numericTerm</a>, <a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">POWER ( <a href="index.html#term">numericTerm</a> , <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -POWER(<a href="index.html#term">numericTerm</a>, <a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns the value of the first argument raised to the power of the second argument as a <code>DOUBLE</code>.</p> -<p>Example:</p> -<p class="notranslate">POWER(number, number)<br />POWER(3, 2)<br />POWER(2, 3)</p> - -<h3 id="ln" class="notranslate">LN</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LN(<a href="index.html#term">numericTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LN ( <a href="index.html#term">numericTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LN(<a href="index.html#term">numericTerm</a>) -</pre> -syntax-end --> -<p>Returns the natural logarithm (base e) of the given positive expression as a <code>DOUBLE</code>.</p> -<p>Example:</p> -<p class="notranslate">LN(number)<br />LN(3)<br />LN(2)</p> - -<h3 id="log" class="notranslate">LOG</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LOG(<a href="index.html#term">numericTerm</a>[, <a href="index.html#term">numericTerm</a>]) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LOG ( <a href="index.html#term">numericTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#term">numericTerm</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LOG(<a href="index.html#term">numericTerm</a>[, <a href="index.html#term">numericTerm</a>]) -</pre> -syntax-end --> -<p>Returns the logarithm of the first argument computed at the base of the second argument as a <code>DOUBLE</code>. If omitted, a base of 10 will be used for the second argument.</p> -<p>Example:</p> -<p class="notranslate">LOG(3, 2)<br />LOG(2, 3)<br />LOG(2)</p> - -<h3 id="round" class="notranslate">ROUND</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -ROUND({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#number">multiplierNumber</a>]]) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">ROUND (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#term">numericTerm</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">dateTimeTerm</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#number">scaleNumber</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d">< table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' DAY '</code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' HOUR '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MINUTE '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' SECOND '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MILLISECOND '</co de></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#number">multiplierNumber</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -ROUND({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#number">multiplierNumber</a>]]) -</pre> -syntax-end --> -<p>Rounds the numeric or timestamp expression to the nearest scale or time unit specified. If the expression is a numeric type, then the second argument is the scale to be used for rounding off the number, defaulting to zero. If the expression is a date/time type, then the second argument may be one of the time units listed to determine the remaining precision of the date/time. A default of <code>MILLISECONDS</code> is used if not present. The multiplier is only applicable for a date/time type and is used to round to a multiple of a time unit (i.e. 10 minute) and defaults to 1 if not specified. This method returns the same type as its first argument.</p> -<p>Example:</p> -<p class="notranslate">ROUND(number)<br />ROUND(number, 2)<br />ROUND(timestamp)<br />ROUND(time, 'HOUR')<br />ROUND(date, 'MINUTE', 30)</p> - -<h3 id="ceil" class="notranslate">CEIL</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -CEIL({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#int">multiplierInt</a>]]) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CEIL (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#term">numericTerm</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">dateTimeTerm</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#number">scaleNumber</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><t able class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' DAY '</code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' HOUR '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MINUTE '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' SECOND '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MILLISECOND '</cod e></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#int">multiplierInt</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -CEIL({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#int">multiplierInt</a>]]) -</pre> -syntax-end --> -<p>Same as <code>ROUND</code>, except it rounds any fractional value up to the next even multiple.</p> -<p>Example:</p> -<p class="notranslate">CEIL(number, 3)<br />CEIL(2.34)<br />CEIL(timestamp, 'SECOND', 30)<br />CEIL(date, 'DAY', 7)</p> - -<h3 id="floor" class="notranslate">FLOOR</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -FLOOR({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#int">multiplierInt</a>]]) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">FLOOR (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#term">numericTerm</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">dateTimeTerm</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#number">scaleNumber</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d">< table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' DAY '</code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' HOUR '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MINUTE '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' SECOND '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MILLISECOND '</co de></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#int">multiplierInt</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -FLOOR({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#int">multiplierInt</a>]]) -</pre> -syntax-end --> -<p>Same as <code>ROUND</code>, except it rounds any fractional value down to the previous even multiple.</p> -<p>Example:</p> -<p class="notranslate">FLOOR(timestamp)<br />FLOOR(date, 'DAY', 7)</p> - -<h3 id="trunc" class="notranslate">TRUNC</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -TRUNC({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#int">multiplierInt</a>]]) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">TRUNC (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#term">numericTerm</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">dateTimeTerm</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#number">scaleNumber</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d">< table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' DAY '</code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' HOUR '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MINUTE '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' SECOND '</code></td></tr></table></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">' MILLISECOND '</co de></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#int">multiplierInt</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -TRUNC({<a href="index.html#term">numericTerm</a> | <a href="index.html#term">dateTimeTerm</a>} [, <a href="index.html#number">scaleNumber</a> | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, <a href="index.html#int">multiplierInt</a>]]) -</pre> -syntax-end --> -<p>Same as <code>FLOOR</code></p> -<p>Example:</p> -<p class="notranslate">TRUNC(timestamp, 'SECOND', 30)<br />TRUNC(date, 'DAY', 7)</p> - -<h3 id="to_number" class="notranslate">TO_NUMBER</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -TO_NUMBER( { <a href="index.html#term">stringTerm</a> | <a href="index.html#term">dateTimeTerm</a> } [, <a href="index.html#string">formatString</a>] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">TO_NUMBER (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#term">stringTerm</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">dateTimeTerm</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#string">formatString</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -TO_NUMBER( { <a href="index.html#term">stringTerm</a> | <a href="index.html#term">dateTimeTerm</a> } [, <a href="index.html#string">formatString</a>] ) -</pre> -syntax-end --> -<p>Formats a string or date/time type as a number, optionally accepting a format string. For details on the format, see <code>java.text.DecimalFormat</code>. For date, time, and timeStamp terms, the result is the time in milliseconds since the epoch. This method returns a decimal number.</p> -<p>Example:</p> -<p class="notranslate">TO_NUMBER('$123.33', '\u00A4###.##')</p> - -<h3 id="rand" class="notranslate">RAND</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -RAND( [<a href="index.html#number">seedNumber</a>] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">RAND (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#number">seedNumber</a></code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -RAND( [<a href="index.html#number">seedNumber</a>] ) -</pre> -syntax-end --> -<p>Function that produces a random, uniformly distributed double value between 0.0 (inclusive) and 1.0 (exclusive). If a seed is provided, then the the returned value is identical across each invocation for the same row. If a seed is not provided, then the returned value is different for each invocation. The seed must be a constant.</p> -<p>Example:</p> -<p class="notranslate">RAND()<br />RAND(5)</p> - -<h3 id="substr" class="notranslate">SUBSTR</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -SUBSTR( <a href="index.html#term">stringTerm</a>, <a href="index.html#int">startInt</a> [, <a href="index.html#int">lengthInt</a> ] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SUBSTR ( <a href="index.html#term">stringTerm</a> , <a href="index.html#int">startInt</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#int">lengthInt</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -SUBSTR( <a href="index.html#term">stringTerm</a>, <a href="index.html#int">startInt</a> [, <a href="index.html#int">lengthInt</a> ] ) -</pre> -syntax-end --> -<p>Returns a substring of a string starting at the one-based position. If zero is used, the position is zero-based. If the start index is negative, then the start index is relative to the end of the string. The length is optional and if not supplied, the rest of the string will be returned.</p> -<p>Example:</p> -<p class="notranslate">SUBSTR('[Hello]', 2, 5)<br />SUBSTR('Hello World', -5)</p> - -<h3 id="instr" class="notranslate">INSTR</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -INSTR( <a href="index.html#term">stringTerm</a>, <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">INSTR ( <a href="index.html#term">stringTerm</a> , <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -INSTR( <a href="index.html#term">stringTerm</a>, <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Returns the one-based position of the initial occurrence of the second argument in the first argument. If the second argument is not contained in the first argument, then zero is returned.</p> -<p>Example:</p> -<p class="notranslate">INSTR('Hello World', 'World')<br />INSTR('Simon says', 'mon')<br />INSTR('Peace on earth', 'war')</p> - -<h3 id="trim" class="notranslate">TRIM</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -TRIM( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">TRIM ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -TRIM( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Removes leading and trailing spaces from the input string.</p> -<p>Example:</p> -<p class="notranslate">TRIM(' Hello ')</p> - -<h3 id="ltrim" class="notranslate">LTRIM</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LTRIM( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LTRIM ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LTRIM( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Removes leading spaces from the input string.</p> -<p>Example:</p> -<p class="notranslate">LTRIM(' Hello')</p> - -<h3 id="rtrim" class="notranslate">RTRIM</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -RTRIM( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">RTRIM ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -RTRIM( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Removes trailing spaces from the input string.</p> -<p>Example:</p> -<p class="notranslate">RTRIM('Hello ')</p> - -<h3 id="lpad" class="notranslate">LPAD</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LPAD( <a href="index.html#term">stringTerm</a>, <a href="index.html#numeric">lengthNumeric</a>, [<a href="index.html#string">padString</a>] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LPAD ( <a href="index.html#term">stringTerm</a> , <a href="index.html#numeric">lengthNumeric</a> ,</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#string">padString</a></code></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LPAD( <a href="index.html#term">stringTerm</a>, <a href="index.html#numeric">lengthNumeric</a>, [<a href="index.html#string">padString</a>] ) -</pre> -syntax-end --> -<p>Pads the string expression with the specific pad character (space by default) up to the length argument.</p> -<p>Example:</p> -<p class="notranslate">LPAD('John',30)</p> - -<h3 id="length" class="notranslate">LENGTH</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LENGTH( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LENGTH ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LENGTH( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Returns the length of the string in characters.</p> -<p>Example:</p> -<p class="notranslate">LENGTH('Hello')</p> - -<h3 id="regexp_substr" class="notranslate">REGEXP_SUBSTR</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -REGEXP_SUBSTR( <a href="index.html#term">stringTerm</a>, <a href="index.html#string">patternString</a> [, <a href="index.html#int">startInt</a> ] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">REGEXP_SUBSTR ( <a href="index.html#term">stringTerm</a> , <a href="index.html#string">patternString</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#int">startInt</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -REGEXP_SUBSTR( <a href="index.html#term">stringTerm</a>, <a href="index.html#string">patternString</a> [, <a href="index.html#int">startInt</a> ] ) -</pre> -syntax-end --> -<p>Returns a substring of a string by applying a regular expression start from the offset of a one-based position. Just like with <code>SUBSTR</code>, if the start index is negative, then it is relative to the end of the string. If not specified, the start index defaults to 1.</p> -<p>Example:</p> -<p class="notranslate">REGEXP_SUBSTR('na1-appsrv35-sj35', '[^-]+') evaluates to 'na1'</p> - -<h3 id="regexp_replace" class="notranslate">REGEXP_REPLACE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -REGEXP_REPLACE( <a href="index.html#term">stringTerm</a>, <a href="index.html#string">patternString</a> [, <a href="index.html#string">replacementString</a> ] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">REGEXP_REPLACE ( <a href="index.html#term">stringTerm</a> , <a href="index.html#string">patternString</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#string">replacementString</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -REGEXP_REPLACE( <a href="index.html#term">stringTerm</a>, <a href="index.html#string">patternString</a> [, <a href="index.html#string">replacementString</a> ] ) -</pre> -syntax-end --> -<p>Returns a string by applying a regular expression and replacing the matches with the replacement string. If the replacement string is not specified, it defaults to an empty string.</p> -<p>Example:</p> -<p class="notranslate">REGEXP_REPLACE('abc123ABC', '[0-9]+', '#') evaluates to 'abc#ABC'</p> - -<h3 id="regexp_split" class="notranslate">REGEXP_SPLIT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -REGEXP_SPLIT( <a href="index.html#term">stringTerm</a>, <a href="index.html#term">patternTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">REGEXP_SPLIT ( <a href="index.html#term">stringTerm</a> , <a href="index.html#term">patternTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -REGEXP_SPLIT( <a href="index.html#term">stringTerm</a>, <a href="index.html#term">patternTerm</a> ) -</pre> -syntax-end --> -<p>Splits a string into a <code>VARCHAR ARRAY</code> using a regular expression. If characters that have a special meaning in regular expressions are to be used as a regular delimiter in the pattern string, they must be escaped with backslashes.</p> -<p>Example:</p> -<p class="notranslate">REGEXP_SPLIT('ONE,TWO,THREE', ',') evaluates to ARRAY['ONE', 'TWO', 'THREE']<br />REGEXP_SPLIT('ONE!#TWO#,!THREE', '[,!#]+') evaluates to ARRAY['ONE', 'TWO', 'THREE']</p> - -<h3 id="upper" class="notranslate">UPPER</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -UPPER( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">UPPER ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -UPPER( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Returns upper case string of the string argument.</p> -<p>Example:</p> -<p class="notranslate">UPPER('Hello')</p> - -<h3 id="lower" class="notranslate">LOWER</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LOWER( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">LOWER ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -LOWER( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Returns lower case string of the string argument.</p> -<p>Example:</p> -<p class="notranslate">LOWER('HELLO')</p> - -<h3 id="reverse" class="notranslate">REVERSE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -REVERSE( <a href="index.html#term">stringTerm</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">REVERSE ( <a href="index.html#term">stringTerm</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -REVERSE( <a href="index.html#term">stringTerm</a> ) -</pre> -syntax-end --> -<p>Returns reversed string of the string argument.</p> -<p>Example:</p> -<p class="notranslate">REVERSE('Hello')</p> - -<h3 id="to_char" class="notranslate">TO_CHAR</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -TO_CHAR( { <a href="index.html#term">timestampTerm</a> | <a href="index.html#term">numberTerm</a> } [, <a href="index.html#string">formatString</a>] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">TO_CHAR (</code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><code class="c"><a href="index.html#term">timestampTerm</a></code></td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><code class="c"><a href="index.html#term">numberTerm</a></code></td><td class="le"></td></tr></table></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#string">formatString</a></code></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -TO_CHAR( { <a href="index.html#term">timestampTerm</a> | <a href="index.html#term">numberTerm</a> } [, <a href="index.html#string">formatString</a>] ) -</pre> -syntax-end --> -<p>Formats a date, time, timestamp, or number as a string. The default date format is <code>yyyy-MM-dd HH:mm:ss</code> and the default number format is <code>#,##0.###</code>. For details, see <code>java.text.SimpleDateFormat</code> for date/time values and <code>java.text.DecimalFormat</code> for numbers. This method returns a string.</p> -<p>Example:</p> -<p class="notranslate">TO_CHAR(myDate, '2001-02-03 04:05:06')<br />TO_CHAR(myDecimal, '#,##0.###')</p> - -<h3 id="to_date" class="notranslate">TO_DATE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -TO_DATE( <a href="index.html#term">stringTerm</a> [, <a href="index.html#string">formatString</a> [, <a href="index.html#string">timeZoneIdString</a>]] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">TO_DATE ( <a href="index.html#term">stringTerm</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#string">formatString</a></code></td><td class="d"><table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"> </td><td class="te"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#string">timeZoneIdString</a></code></td></tr></table></td><td class="le"></td></tr></table></td></tr></table></td><td class="le"></td></tr></table></td><td class="d"><code class="c">)</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -TO_DATE( <a href="index.html#term">stringTerm</a> [, <a href="index.html#string">formatString</a> [, <a href="index.html#string">timeZoneIdString</a>]] ) -</pre> -syntax-end --> -<p>Parses a string and returns a date. Note that the returned date is internally represented as the number of milliseconds since the java epoch. The most important format characters are: y year, M month, d day, H hour, m minute, s second. The default format string is <code>yyyy-MM-dd HH:mm:ss</code>. For details of the format, see <code>java.text.SimpleDateFormat</code>. By default, <code>GMT</code> will be used as the time zone when parsing the date. However, a time zone id can also be supplied. This is a time zone id such as '<code>GMT</code>+1'. If 'local' is provided as the time zone id, the local time zone will be used for parsing. The configuration setting <code>phoenix.query.dateFormatTimeZone</code> can also be set to a time zone id, which will cause the default of <code>GMT</code> to be overridden with the configured time zone id.</p> -<p>Example:</p> -<p class="notranslate">TO_DATE('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z')<br />TO_DATE('1970-01-01', 'yyyy-MM-dd', 'GMT+1')</p> - -<h3 id="current_date" class="notranslate">CURRENT_DATE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -CURRENT_DATE() -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CURRENT_DATE ( )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -CURRENT_DATE() -</pre> -syntax-end --> -<p>Returns the current server-side date, bound at the start of the execution of a query based on the current time on the region server owning the metadata of the table being queried.</p> -<p>Example:</p> -<p class="notranslate">CURRENT_DATE()</p> - -<h3 id="current_time" class="notranslate">CURRENT_TIME</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -CURRENT_TIME() -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">CURRENT_TIME ( )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -CURRENT_TIME() -</pre> -syntax-end --> -<p>Same as <code>CURRENT_DATE</code>(), except returns a value of type <code>TIME</code>. In either case, the underlying representation is the epoch time as a long value.</p> -<p>Example:</p> -<p class="notranslate">CURRENT_TIME()</p> - -<h3 id="convert_tz" class="notranslate">CONVERT_TZ</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -CONVERT_TZ(<a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>, <a href="index.html#string">fromTimeZoneString</a>, <a href="index.html#string">toTimeZoneString</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">CONVERT_TZ ( <a href="index.html#term">dateTerm</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#term">timeTerm</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#term">timestampTerm</a> , <a href="index.html#string">fromTimeZoneString</a> , <a href="index.html#string">toTimeZoneString</a> )</code></td></tr></table></td><td class="le"></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -CONVERT_TZ(<a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>, <a href="index.html#string">fromTimeZoneString</a>, <a href="index.html#string">toTimeZoneString</a>) -</pre> -syntax-end --> -<p>Converts date/time from one time zone to another returning the shifted date/time value.</p> -<p>Example:</p> -<p class="notranslate">CONVERT_TZ(myDate, 'UTC', 'Europe/Prague')</p> - -<h3 id="timezone_offset" class="notranslate">TIMEZONE_OFFSET</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -TIMEZONE_OFFSET(<a href="index.html#string">timeZoneString</a>, <a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">TIMEZONE_OFFSET ( <a href="index.html#string">timeZoneString</a> , <a href="index.html#term">dateTerm</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#term">timeTerm</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#term">timestampTerm</a> )</code></td></tr></table></td><td class="le"></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -TIMEZONE_OFFSET(<a href="index.html#string">timeZoneString</a>, <a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>) -</pre> -syntax-end --> -<p>Returns offset (shift in minutes) of a time zone at particular date/time in minutes.</p> -<p>Example:</p> -<p class="notranslate">TIMEZONE_OFFSET('Indian/Cocos', myDate)</p> - -<h3 id="now" class="notranslate">NOW</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -NOW() -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">NOW ( )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -NOW() -</pre> -syntax-end --> -<p>Returns the current date, bound at the start of the execution of a query based on the current time on the region server owning the metadata of the table being queried.</p> -<p>Example:</p> -<p class="notranslate">NOW()</p> - -<h3 id="year" class="notranslate">YEAR</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -YEAR(<a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">YEAR ( <a href="index.html#term">dateTerm</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#term">timeTerm</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#term">timestampTerm</a> )</code></td></tr></table></td><td class="le"></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -YEAR(<a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>) -</pre> -syntax-end --> -<p>Returns the year of the specified date.</p> -<p>Example:</p> -<p class="notranslate">YEAR(TO_DATE('2015-6-05'))</p> - -<h3 id="month" class="notranslate">MONTH</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -MONTH(<a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="ts"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">MONTH ( <a href="index.html#term">dateTerm</a></code></td></tr></table></td><td class="te"></td></tr><tr class="railroad"><td class="ks"></td><td class="d"><code class="c"><a href="index.html#term">timeTerm</a></code></td><td class="ke"></td></tr><tr class="railroad"><td class="ls"></td><td class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c"><a href="index.html#term">timestampTerm</a> )</code></td></tr></table></td><td class="le"></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -MONTH(<a href="index.html#term">dateTerm</a>|<a href="index.html#term">timeTerm</a>|<a href="index.html#term">timestampTerm</a>) -</pre> -syntax-end --> -<p>Returns the month of the specified date.</p> -<p>Example:</p>
[... 100 lines stripped ...]
