Modified: phoenix/site/publish/language/functions.html URL: http://svn.apache.org/viewvc/phoenix/site/publish/language/functions.html?rev=1899720&r1=1899719&r2=1899720&view=diff ============================================================================== --- phoenix/site/publish/language/functions.html (original) +++ phoenix/site/publish/language/functions.html Sun Apr 10 21:53:25 2022 @@ -1,7 +1,7 @@ <!DOCTYPE html> <!-- - Generated by Apache Maven Doxia at 2022-04-05 + Generated by Apache Maven Doxia at 2022-04-10 Rendered using Reflow Maven Skin 1.1.0 (http://andriusvelykis.github.io/reflow-maven-skin) --> <html xml:lang="en" lang="en"> @@ -168,1886 +168,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="#approx_count_distinct" >APPROX_COUNT_DISTINCT</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="#first_values" >FIRST_VALUES</a><br /> - - <a href="#last_values" >LAST_VALUES</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="#approx_count_distinct" >APPROX_COUNT_DISTINCT</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 /> - - <a href="#first_values" >FIRST_VALUES</a><br /> - </td><td class="index"> - - <a href="#last_values" >LAST_VALUES</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 /> - </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 /> - - <a href="#collation_key" >COLLATION_KEY</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 /> - - <a href="#collation_key" >COLLATION_KEY</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="#to_time" >TO_TIME</a><br /> - - <a href="#to_timestamp" >TO_TIMESTAMP</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="#dayofyear" >DAYOFYEAR</a><br /> - - <a href="#dayofmonth" >DAYOFMONTH</a><br /> - - <a href="#dayofweek" >DAYOFWEEK</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="#to_time" >TO_TIME</a><br /> - - <a href="#to_timestamp" >TO_TIMESTAMP</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="#dayofyear" >DAYOFYEAR</a><br /> - - <a href="#dayofmonth" >DAYOFMONTH</a><br /> - </td><td class="index"> - - <a href="#dayofweek" >DAYOFWEEK</a><br /> - - <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 /> - - <a href="#get_bit" >GET_BIT</a><br /> - - <a href="#get_byte" >GET_BYTE</a><br /> - - <a href="#octet_length" >OCTET_LENGTH</a><br /> - - <a href="#set_bit" >SET_BIT</a><br /> - - <a href="#set_byte" >SET_BYTE</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 /> - - <a href="#encode" >ENCODE</a><br /> - - <a href="#decode" >DECODE</a><br /> - </td><td class="index"> - - <a href="#coalesce" >COALESCE</a><br /> - - <a href="#get_bit" >GET_BIT</a><br /> - - <a href="#get_byte" >GET_BYTE</a><br /> - - <a href="#octet_length" >OCTET_LENGTH</a><br /> - </td><td class="index"> - - <a href="#set_bit" >SET_BIT</a><br /> - - <a href="#set_byte" >SET_BYTE</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="approx_count_distinct" class="notranslate">APPROX_COUNT_DISTINCT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -APPROX_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">APPROX_COUNT_DISTINCT (</code></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> -APPROX_COUNT_DISTINCT( { * | { <a href="index.html#term">term</a> } } ) -</pre> -syntax-end --> -<p>The approximate distinct count of all row, or of the non-null values. The relative error of approximation by default is less than 0.00405 This method returns a long. If no rows are selected, the result is 0. Aggregates are only allowed in select statements.</p> -<p>Example:</p> -<p class="notranslate">APPROX_COUNT_DISTINCT(*)</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="first_values" class="notranslate">FIRST_VALUES</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -FIRST_VALUES( { <a href="index.html#expression">expression</a> , <a href="index.html#numeric">numeric</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_VALUES ( <a href="index.html#expression">expression</a> , <a href="index.html#numeric">numeric</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_VALUES( { <a href="index.html#expression">expression</a> , <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>Returns an array of at most the given numeric size of the first values in each distinct group ordered according to the <code>ORDER BY</code> specification.</p> -<p>Example:</p> -<p class="notranslate">FIRST_VALUES( name, 3 ) WITHIN GROUP (ORDER BY salary DESC)</p> - -<h3 id="last_values" class="notranslate">LAST_VALUES</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LAST_VALUES( { <a href="index.html#expression">expression</a> , <a href="index.html#numeric">numeric</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_VALUES ( <a href="index.html#expression">expression</a> , <a href="index.html#numeric">numeric</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_VALUES( { <a href="index.html#expression">expression</a> , <a href="index.html#numeric">numeric</a> } ) WITHIN GROUP (ORDER BY { <a href="index.html#expression">expression</a> } { ASC | DESC } ) -</pre> -syntax-end --> -<p>Returns an array of at most the given numeric size of the last values in each distinct group ordered according to the <code>ORDER BY</code> specification.</p> -<p>Example:</p> -<p class="notranslate">LAST_VALUES( name, 3 ) 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="get_bit" class="notranslate">GET_BIT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -GET_BIT( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">GET_BIT ( <a href="index.html#value">binaryValue</a> , <a href="index.html#int">offsetInt</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -GET_BIT( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a> ) -</pre> -syntax-end --> -<p>Retrieves the bit at the given index in the given binary value.</p> -<p>Example:</p> -<p class="notranslate">GET_BIT(CAST('FFFF' as BINARY), 1)</p> - -<h3 id="get_byte" class="notranslate">GET_BYTE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -GET_BYTE( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">GET_BYTE ( <a href="index.html#value">binaryValue</a> , <a href="index.html#int">offsetInt</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -GET_BYTE( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a> ) -</pre> -syntax-end --> -<p>Retrieves the byte at the given index in the given binary value.</p> -<p>Example:</p> -<p class="notranslate">GET_BYTE(CAST('FFFF' as BINARY), 1)</p> - -<h3 id="octet_length" class="notranslate">OCTET_LENGTH</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -OCTET_LENGTH( <a href="index.html#value">binaryValue</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">OCTET_LENGTH ( <a href="index.html#value">binaryValue</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -OCTET_LENGTH( <a href="index.html#value">binaryValue</a> ) -</pre> -syntax-end --> -<p>Returns the number of bytes in a binary value.</p> -<p>Example:</p> -<p class="notranslate">OCTET_LENGTH(NAME)</p> - -<h3 id="set_bit" class="notranslate">SET_BIT</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -SET_BIT( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a>, <a href="index.html#value">newValue</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SET_BIT ( <a href="index.html#value">binaryValue</a> , <a href="index.html#int">offsetInt</a> , <a href="index.html#value">newValue</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -SET_BIT( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a>, <a href="index.html#value">newValue</a> ) -</pre> -syntax-end --> -<p>Replaces the bit at the given index in the binary value with the provided newValue.</p> -<p>Example:</p> -<p class="notranslate">SET_BIT(CAST('FFFF' as BINARY), 1, 61)</p> - -<h3 id="set_byte" class="notranslate">SET_BYTE</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -SET_BYTE( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a>, <a href="index.html#value">newValue</a> ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">SET_BYTE ( <a href="index.html#value">binaryValue</a> , <a href="index.html#int">offsetInt</a> , <a href="index.html#value">newValue</a> )</code></td></tr></table> -</div> -<!-- railroad-end --> -<!-- syntax-start -<pre> -SET_BYTE( <a href="index.html#value">binaryValue</a>, <a href="index.html#int">offsetInt</a>, <a href="index.html#value">newValue</a> ) -</pre> -syntax-end --> -<p>Replaces the byte at the given index in the binary value with the provided newValue.</p> -<p>Example:</p> -<p class="notranslate">SET_BYTE(CAST('FFFF' as BINARY), 1, 61)</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> [, <a href="index.html#string">localeString</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><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">localeString</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> -UPPER( <a href="index.html#term">stringTerm</a> [, <a href="index.html#string">localeString</a> ] ) -</pre> -syntax-end --> -<p>Returns upper case string of the string argument. If localeString (available in Phoenix 4.14) is provided, it identifies the locale whose rules are used for the conversion. If localeString is not provided, the default locale is used. The localeString must be of the form returned by the Java 6 implementation of java.util.Locale.toString() e.g. 'zh_<code>TW_STROKE</code>' or 'en_<code>US</code>' or 'fr_<code>FR</code>'.</p> -<p>Example:</p> -<p class="notranslate">UPPER('Hello')<br />UPPER('Hello', 'tr_TR')</p> - -<h3 id="lower" class="notranslate">LOWER</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -LOWER( <a href="index.html#term">stringTerm</a> [, <a href="index.html#string">localeString</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><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">localeString</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> -LOWER( <a href="index.html#term">stringTerm</a> [, <a href="index.html#string">localeString</a> ] ) -</pre> -syntax-end --> -<p>Returns lower case string of the string argument. If localeString (available in Phoenix 4.14) is provided, it identifies the locale whose rules are used for the conversion. If localeString is not provided, the default locale is used. The localeString must be of the form returned by the Java 6 implementation of java.util.Locale.toString() e.g. 'zh_<code>TW_STROKE</code>' or 'en_<code>US</code>' or 'fr_<code>FR</code>'.</p> -<p>Example:</p> -<p class="notranslate">LOWER('HELLO')<br />LOWER('HELLO', 'en_US')</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="collation_key" class="notranslate">COLLATION_KEY</h3> -<!-- railroad-start --> -<pre name="bnf" style="display: none"> -COLLATION_KEY( <a href="index.html#term">stringTerm</a>, <a href="index.html#string">localeString</a> [, <a href="index.html#boolean">upperCaseBoolean</a> [, <a href="index.html#int">strengthInt</a> [, <a href="index.html#int">decompositionInt</a> ] ] ] ) -</pre> -<div name="railroad"> -<table class="railroad"><tr class="railroad"><td class="d"><code class="c">COLLATION_KEY ( <a href="index.html#term">stringTerm</a> , <a href="index.html#string">localeString</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#boolean">upperCaseBoolean</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">strengthInt</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><t d class="d"><table class="railroad"><tr class="railroad"><td class="d"><code class="c">, <a href="index.html#int">decompositionInt</a></code></td></tr></table></td><td class="le"></td></tr></table></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> -COLLATION_KEY( <a href="index.html#term">stringTerm</a>, <a href="index.html#string">localeString</a> [, <a href="index.html#boolean">upperCaseBoolean</a> [, <a href="index.html#int">strengthInt</a> [, <a href="index.html#int">decompositionInt</a> ] ] ] ) -</pre> -syntax-end -->
[... 300 lines stripped ...]
