# Couchbase

### SELECT Statements <a href="#default" id="default"></a>

A SELECT statement can consist of the following basic clauses.

* SELECT
* INTO
* FROM
* JOIN
* WHERE
* GROUP BY
* HAVING
* UNION
* ORDER BY
* LIMIT

### SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:<br>

\| <p><code>SELECT</code> <code>{</code></p><p>  <code>\[ TOP</code> <code>\<numeric\_literal> | DISTINCT</code> <code>]</code></p><p>  <code>{</code></p><p>    <code>*</code></p><p>    <code>| {</code></p><p>        <code>\<expression> \[ \[ AS</code> <code>] \<column\_reference> ]</code></p><p>        <code>| { \<table\_name> | \<correlation\_name> } .*</code></p><p>      <code>} \[ , ... ]</code></p><p>  <code>}</code></p><p>  <code>\[ INTO</code> <code>csv:// \[ filename= ] \<file\_path> \[ ;delimiter=tab ] ]</code></p><p>  <code>{</code></p><p>    <code>FROM</code> <code>\<table\_reference> \[ \[ AS</code> <code>] \<identifier> ]</code></p><p>  <code>} \[ , ... ]</code></p><p>  <code>\[ \[</code> </p><p>      <code>INNER</code> <code>| { { LEFT</code> <code>| RIGHT</code> <code>| FULL</code> <code>} \[ OUTER</code> <code>] }</code></p><p>    <code>] JOIN</code> <code>\<table\_reference> \[ ON</code> <code>\<search\_condition> ] \[ \[ AS</code> <code>] \<identifier> ]</code></p><p>  <code>] \[ ... ]</code></p><p>  <code>\[ WHERE</code> <code>\<search\_condition> ]</code></p><p>  <code>\[ GROUP</code> <code>BY</code> <code>\<column\_reference> \[ , ... ]</code></p><p>  <code>\[ HAVING</code> <code>\<search\_condition> ]</code></p><p>  <code>\[ UNION</code> <code>\[ ALL</code> <code>] \<select\_statement> ]</code></p><p>  <code>\[</code></p><p>    <code>ORDER</code> <code>BY</code></p><p>    <code>\<column\_reference> \[ ASC</code> <code>| DESC</code> <code>] \[ NULLS FIRST</code> <code>| NULLS LAST</code> <code>]</code></p><p>  <code>]</code></p><p>  <code>\[</code></p><p>    <code>LIMIT \<expression></code></p><p>    <code>\[</code></p><p>      <code>{ OFFSET | , }</code></p><p>      <code>\<expression></code></p><p>    <code>]</code></p><p>  <code>]</code></p><p><code>} | SCOPE\_IDENTITY()</code></p><p> </p><p><code>\<expression> ::=</code></p><p>  <code>| \<column\_reference></code></p><p>  <code>| @ \<parameter></code></p><p>  <code>| ?</code></p><p>  <code>| COUNT( \* | { \[ DISTINCT</code> <code>] \<expression> } )</code></p><p>  <code>| { AVG</code> <code>| MAX</code> <code>| MIN</code> <code>| SUM</code> <code>| COUNT</code> <code>} ( \<expression> )</code></p><p>  <code>| NULLIF</code> <code>( \<expression> , \<expression> )</code></p><p>  <code>| COALESCE</code> <code>( \<expression> , ... )</code></p><p>  <code>| CASE</code> <code>\<expression></code></p><p>      <code>WHEN</code> <code>{ \<expression> | \<search\_condition> } THEN</code> <code>{ \<expression> | NULL</code> <code>} \[ ... ]</code></p><p>    <code>\[ ELSE</code> <code>{ \<expression> | NULL</code> <code>} ]</code></p><p>    <code>END</code></p><p>  <code>| \<literal></code></p><p>  <code>| \<sql\_function></code></p><p> </p><p><code>\<search\_condition> ::=</code></p><p>  <code>{</code></p><p>    <code>\<expression> { = | > | < | >= | <= | <> | != | LIKE</code> <code>| NOT</code> <code>LIKE</code> <code>| IN</code> <code>| NOT</code> <code>IN</code> <code>| IS</code> <code>NULL</code> <code>| IS</code> <code>NOT</code> <code>NULL</code> <code>| AND</code> <code>| OR</code> <code>| CONTAINS</code> <code>| BETWEEN</code> <code>} \[ \<expression> ]</code></p><p>  <code>} \[ { AND</code> <code>| OR</code> <code>} ... ]</code></p> |
\| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

#### Examples

1. Return all columns:<br>

   | `SELECT * FROM Customer` |
   | ------------------------ |
2. Rename a column:<br>

   | `SELECT [TotalDue] AS MY_TotalDue FROM Customer` |
   | ------------------------------------------------ |
3. Cast a column's data as a different data type:<br>

   | `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Customer` |
   | -------------------------------------------------------------------------- |
4. Search data:<br>

   | `SELECT * FROM Customer WHERE CustomerId = '12345'` |
   | --------------------------------------------------- |
5. The Couchbase APIs support the following operators in the WHERE clause: =, !=, <, <=, >, >=, IS NULL, LIKE, AND, OR, NOT, IN.<br>

   | `SELECT * FROM Customer WHERE CustomerId = '12345';` |
   | ---------------------------------------------------- |
6. Return the number of items matching the query criteria:<br>

   | `SELECT COUNT(*) AS MyCount FROM Customer` |
   | ------------------------------------------ |
7. Return the number of unique items matching the query criteria:<br>

   | `SELECT COUNT(DISTINCT TotalDue) FROM Customer` |
   | ----------------------------------------------- |
8. Return the unique items matching the query criteria:<br>

   | `SELECT DISTINCT TotalDue FROM Customer` |
   | ---------------------------------------- |
9. Summarize data:<br>

   | `SELECT TotalDue, MAX(AnnualRevenue) FROM Customer  GROUP BY TotalDue` |
   | ---------------------------------------------------------------------- |

   See Aggregate Functions below for details.
10. Retrieve data from multiple tables.<br>

    | `SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId` |
    | -------------------------------------------------------------------------------------------------------------------- |

    See JOIN Queries below for details.
11. Sort a result set in ascending order:<br>

    | `SELECT Name, TotalDue FROM Customer  ORDER BY TotalDue ASC` |
    | ------------------------------------------------------------ |
12. Restrict a result set to the specified number of rows:<br>

    | `SELECT Name, TotalDue FROM Customer LIMIT 10` |
    | ---------------------------------------------- |
13. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.<br>

    | `SELECT * FROM Customer WHERE CustomerId = @param` |
    | -------------------------------------------------- |

### Aggregate Functions <a href="#default" id="default"></a>

#### Examples of Aggregate Functions <a href="#examples-of-aggregate-functions" id="examples-of-aggregate-functions"></a>

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

#### COUNT <a href="#count" id="count"></a>

Returns the number of rows matching the query criteria.<br>

| `SELECT COUNT(*) FROM Customer WHERE CustomerId = '12345'` |
| ---------------------------------------------------------- |

#### COUNT(DISTINCT) <a href="#countdistinct" id="countdistinct"></a>

Returns the number of distinct, non-null field values matching the query criteria.<br>

| `SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Customer WHERE CustomerId = '12345'` |
| ---------------------------------------------------------------------------------------- |

#### AVG <a href="#avg" id="avg"></a>

Returns the average of the column values.<br>

| `SELECT TotalDue, AVG(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'` `GROUP BY TotalDue` |
| -------------------------------------------------------------------------------------------------- |

#### MIN <a href="#min" id="min"></a>

Returns the minimum column value.<br>

| `SELECT MIN(AnnualRevenue), TotalDue FROM Customer WHERE CustomerId = '12345'` `GROUP BY TotalDue` |
| -------------------------------------------------------------------------------------------------- |

#### MAX <a href="#max" id="max"></a>

Returns the maximum column value.<br>

| `SELECT TotalDue, MAX(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'` `GROUP BY TotalDue` |
| -------------------------------------------------------------------------------------------------- |

#### SUM <a href="#sum" id="sum"></a>

Returns the total sum of the column values.<br>

| `SELECT SUM(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'` |
| -------------------------------------------------------------------- |

#### COUNT <a href="#count" id="count"></a>

Returns the number of rows matching the query criteria.<br>

| `SELECT COUNT(*) FROM Customer WHERE CustomerId = '12345'` |
| ---------------------------------------------------------- |

#### COUNT(DISTINCT) <a href="#countdistinct" id="countdistinct"></a>

Returns the number of distinct, non-null field values matching the query criteria.<br>

| `SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Customer WHERE CustomerId = '12345'` |
| ---------------------------------------------------------------------------------------- |

#### AVG <a href="#avg" id="avg"></a>

Returns the average of the column values.<br>

| `SELECT TotalDue, AVG(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'`  `GROUP BY TotalDue` |
| --------------------------------------------------------------------------------------------------- |

#### MIN <a href="#min" id="min"></a>

Returns the minimum column value.<br>

| `SELECT MIN(AnnualRevenue), TotalDue FROM Customer WHERE CustomerId = '12345'` `GROUP BY TotalDue` |
| -------------------------------------------------------------------------------------------------- |

#### MAX <a href="#max" id="max"></a>

Returns the maximum column value.<br>

| `SELECT TotalDue, MAX(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'` `GROUP BY TotalDue` |
| -------------------------------------------------------------------------------------------------- |

#### SUM <a href="#sum" id="sum"></a>

Returns the total sum of the column values.<br>

| `SELECT SUM(AnnualRevenue) FROM Customer WHERE CustomerId = '12345'` |
| -------------------------------------------------------------------- |

### JOIN Queries <a href="#default" id="default"></a>

The Provider for Couchbase supports standard SQL joins like the following examples.

#### Inner Join

An inner join selects only rows from both tables that match the join condition:<br>

| `SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId` |
| -------------------------------------------------------------------------------------------------------------------- |

#### Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:<br>

| `SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId` |
| -------------------------------------------------------------------------------------------------------------------------------- |

### Date Literal Functions <a href="#default" id="default"></a>

The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.

#### L\_TODAY() <a href="#ltoday" id="ltoday"></a>

The current day.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` |
| ----------------------------------------------------- |

#### L\_YESTERDAY() <a href="#lyesterday" id="lyesterday"></a>

The previous day.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` |
| --------------------------------------------------------- |

#### L\_TOMORROW() <a href="#ltomorrow" id="ltomorrow"></a>

The following day.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` |
| -------------------------------------------------------- |

#### L\_LAST\_WEEK() <a href="#llastweek" id="llastweek"></a>

Every day in the preceding week.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` |
| --------------------------------------------------------- |

#### L\_THIS\_WEEK() <a href="#lthisweek" id="lthisweek"></a>

Every day in the current week.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` |
| --------------------------------------------------------- |

#### L\_NEXT\_WEEK() <a href="#lnextweek" id="lnextweek"></a>

Every day in the following week.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()` |
| --------------------------------------------------------- |

Also available:

* L\_LAST/L\_THIS/L\_NEXT MONTH
* L\_LAST/L\_THIS/L\_NEXT QUARTER
* L\_LAST/L\_THIS/L\_NEXT YEAR

#### L\_LAST\_N\_DAYS(n) <a href="#llastndaysn" id="llastndaysn"></a>

The previous n days, excluding the current day.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)` |
| ------------------------------------------------------------ |

#### L\_NEXT\_N\_DAYS(n) <a href="#lnextndaysn" id="lnextndaysn"></a>

The following n days, including the current day.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)` |
| ------------------------------------------------------------ |

Also available:

* L\_LAST/L\_NEXT\_90\_DAYS

#### L\_LAST\_N\_WEEKS(n) <a href="#llastnweeksn" id="llastnweeksn"></a>

Every day in every week, starting n weeks before current week, and ending in the previous week.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)` |
| ------------------------------------------------------------- |

#### L\_NEXT\_N\_WEEKS(n) <a href="#lnextnweeksn" id="lnextnweeksn"></a>

Every day in every week, starting the following week, and ending n weeks in the future.<br>

| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)` |
| ------------------------------------------------------------- |

Also available:

* L\_LAST/L\_NEXT\_N\_MONTHS(n)
* L\_LAST/L\_NEXT\_N\_QUARTERS(n)
* L\_LAST/L\_NEXT\_N\_YEARS(n)

### Projection Functions <a href="#default" id="default"></a>

#### ARRAY\_AGG(column) <a href="#arrayaggcolumn" id="arrayaggcolumn"></a>

Returns array of the non-MISSING values in the group, including NULL values.

* column: Any column expression.

#### ARRAY\_APPEND(column, value) <a href="#arrayappendcolumn-value" id="arrayappendcolumn-value"></a>

Returns new array with value appended.

* column: Any column expression.
* value: The value to be appended to the array.

#### ARRAY\_CONCAT(column1, column2) <a href="#arrayconcatcolumn1-column2" id="arrayconcatcolumn1-column2"></a>

Returns new array with the concatenation of the input arrays.

* column1: Any column expression.
* column2: Any column expression.

#### ARRAY\_DISTINCT(column) <a href="#arraydistinctcolumn" id="arraydistinctcolumn"></a>

Returns new array with distinct elements of input array.

* column: Any column expression.

#### ARRAY\_IFNULL(column) <a href="#arrayifnullcolumn" id="arrayifnullcolumn"></a>

Returns the first non-NULL value in the array, or NULL.

* column: Any column expression.

#### ARRAY\_PREPEND(column, value) <a href="#arrayprependcolumn-value" id="arrayprependcolumn-value"></a>

Returns new array with value pre-pended.

* column: Any column expression.
* value: The value to be pre-pended to the array.

#### ARRAY\_PUT(column, value) <a href="#arrayputcolumn-value" id="arrayputcolumn-value"></a>

Returns new array with value appended, if value is not already present, otherwise returns the unmodified input array.

* column: Any column expression.
* value: The value to append to the array.

#### ARRAY\_REMOVE(column, value) <a href="#arrayremovecolumn-value" id="arrayremovecolumn-value"></a>

Returns new array with all occurrences of value removed.

* column: Any column expression.
* value: The value to be removed from the array.

#### ARRAY\_REPLACE(column, value1, value2 \[, integer\_n]) <a href="#arrayreplacecolumn-value1-value2--integern" id="arrayreplacecolumn-value1-value2--integern"></a>

Returns new array with all occurrences of value removed.

* column: Any column expression.
* value1: The value to be replaced by value2.
* value2: The value to replace value1.
* integer\_n: The maximum number of replacements to be performed.

#### ARRAY\_REVERSE(column) <a href="#arrayreversecolumn" id="arrayreversecolumn"></a>

Returns new array with all elements in reverse order.

* column: Any column expression.

#### ARRAY\_SORT(column) <a href="#arraysortcolumn" id="arraysortcolumn"></a>

Returns new array with elements sorted in N1QL collation order.

* column: Any column expression.

#### DECODE\_JSON(column) <a href="#decodejsoncolumn" id="decodejsoncolumn"></a>

Unmarshals the JSON-encoded string into a N1QL value. The empty string is MISSING.

* column: Any column expression.

#### ENCODE\_JSON(column) <a href="#encodejsoncolumn" id="encodejsoncolumn"></a>

Marshals the N1QL value into a JSON-encoded string. MISSING becomes the empty string.

* column: Any column expression.

#### ENCODED\_SIZE(column) <a href="#encodedsizecolumn" id="encodedsizecolumn"></a>

Number of bytes in an uncompressed JSON encoding of the value. The exact size is implementation-dependent. Always returns an integer, and never MISSING or NULL. Returns 0 for MISSING.

* column: Any column expression.

#### POLY\_LENGTH(column) <a href="#polylengthcolumn" id="polylengthcolumn"></a>

Returns length of the value after evaluating the expression. The exact meaning of length depends on the type of the value: MISSING: MISSING; NULL: NULL; String: The length of the string.; Array: The number of elements in the array.; Object: The number of name/value pairs in the object; Any other value: NULL.

* column: Any column expression.

#### OBJECT\_LENGTH(column) <a href="#objectlengthcolumn" id="objectlengthcolumn"></a>

Returns number of name-value pairs in the object.

* column: Any column expression.

#### OBJECT\_NAMES(column) <a href="#objectnamescolumn" id="objectnamescolumn"></a>

Returns array containing the attribute names of the object, in N1QL collation order.

* column: Any column expression.

#### OBJECT\_PAIRS(column) <a href="#objectpairscolumn" id="objectpairscolumn"></a>

Returns array containing the attribute name and value pairs of the object, in N1QL collation order of the names.

* column: Any column expression.

#### OBJECT\_VALUES(column) <a href="#objectvaluescolumn" id="objectvaluescolumn"></a>

Returns array containing the attribute values of the object, in N1QL collation order of the corresponding names.

* column: Any column expression.

#### ARRAY\_AVG(column) <a href="#arrayavgcolumn" id="arrayavgcolumn"></a>

Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

* column: Any column expression.

#### ARRAY\_CONTAINS(column, value) <a href="#arraycontainscolumn-value" id="arraycontainscolumn-value"></a>

Returns true if the array contains value.

* column: Any column expression.
* value: The value contained within the array.

#### ARRAY\_COUNT(column) <a href="#arraycountcolumn" id="arraycountcolumn"></a>

Returns count of all the non-NULL values in the array, or zero if there are no such values.

* column: Any column expression.

#### ARRAY\_LENGTH(column) <a href="#arraylengthcolumn" id="arraylengthcolumn"></a>

Returns the number of elements in the array.

* column: Any column expression.

#### ARRAY\_MAX(column) <a href="#arraymaxcolumn" id="arraymaxcolumn"></a>

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.

* column: Any column expression.

#### ARRAY\_MIN(column) <a href="#arraymincolumn" id="arraymincolumn"></a>

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.

* column: Any column expression.

#### ARRAY\_POSITION(column, value) <a href="#arraypositioncolumn-value" id="arraypositioncolumn-value"></a>

Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.

* column: Any column expression.
* value: The value contained within the array.

#### ARRAY\_SUM(column) <a href="#arraysumcolumn" id="arraysumcolumn"></a>

Sum of all the non-NULL number values in the array, or zero if there are no such values.

* column: Any column expression.

#### GREATEST(column1, column2 \[,column3 \[,column4]]) <a href="#greatestcolumn1-column2-column3-column4" id="greatestcolumn1-column2-column3-column4"></a>

Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### LEAST(column1, column2 \[,column3 \[,column4]]) <a href="#leastcolumn1-column2-column3-column4" id="leastcolumn1-column2-column3-column4"></a>

Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFMISSING(column1, column2 \[,column3 \[,column4]]) <a href="#ifmissingcolumn1-column2-column3-column4" id="ifmissingcolumn1-column2-column3-column4"></a>

Returns the first non-MISSING value.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFMISSINGORNULL(column1, column2 \[,column3 \[,column4]]) <a href="#ifmissingornullcolumn1-column2-column3-column4" id="ifmissingornullcolumn1-column2-column3-column4"></a>

Returns first non-NULL, non-MISSING value.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFNULL(column1, column2 \[,column3 \[,column4]]) <a href="#ifnullcolumn1-column2-column3-column4" id="ifnullcolumn1-column2-column3-column4"></a>

Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### MISSINGIF(column1, column2) <a href="#missingifcolumn1-column2" id="missingifcolumn1-column2"></a>

Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.

#### NULLIF(column1, column2) <a href="#nullifcolumn1-column2" id="nullifcolumn1-column2"></a>

Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.

#### IFINF(column1, column2 \[,column3 \[,column4]]) <a href="#ifinfcolumn1-column2-column3-column4" id="ifinfcolumn1-column2-column3-column4"></a>

Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFNAN(column1, column2 \[,column3 \[,column4]]) <a href="#ifnancolumn1-column2-column3-column4" id="ifnancolumn1-column2-column3-column4"></a>

Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFNANORINF(column1, column2 \[,column3 \[,column4]]) <a href="#ifnanorinfcolumn1-column2-column3-column4" id="ifnanorinfcolumn1-column2-column3-column4"></a>

Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### NANIF(column1, column2 \[,column3 \[,column4]]) <a href="#nanifcolumn1-column2-column3-column4" id="nanifcolumn1-column2-column3-column4"></a>

Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### NEGINFIF(column1, column2 \[,column3 \[,column4]]) <a href="#neginfifcolumn1-column2-column3-column4" id="neginfifcolumn1-column2-column3-column4"></a>

Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### POSINFIF(column1, column2 \[,column3 \[,column4]]) <a href="#posinfifcolumn1-column2-column3-column4" id="posinfifcolumn1-column2-column3-column4"></a>

Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### CLOCK\_MILLIS() <a href="#clockmillis" id="clockmillis"></a>

Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.

#### CLOCK\_STR(\[string\_fmt]) <a href="#clockstrstringfmt" id="clockstrstringfmt"></a>

Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.

* string\_fmt: The datetime format to return the system clock in.

#### DATE\_ADD\_MILLIS(column, integer\_n, string\_part) <a href="#dateaddmilliscolumn-integern-stringpart" id="dateaddmilliscolumn-integern-stringpart"></a>

Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.

* column: Any column expression.
* integer\_n: The number of string\_part's to add to the column value.
* string\_part: The part to add integer\_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_ADD\_STR(column, integer\_n, string\_part) <a href="#dateaddstrcolumn-integern-stringpart" id="dateaddstrcolumn-integern-stringpart"></a>

Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.

* column: Any column expression.
* integer\_n: The number of string\_part's to add to the column value.
* string\_part: The part to add integer\_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_DIFF\_MILLIS(column1, column2, string\_part) <a href="#datediffmilliscolumn1-column2-stringpart" id="datediffmilliscolumn1-column2-stringpart"></a>

Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.

* column1: Any column expression.
* column2: Any column expression.
* string\_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_DIFF\_STR(column1, column2, string\_part) <a href="#datediffstrcolumn1-column2-stringpart" id="datediffstrcolumn1-column2-stringpart"></a>

Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.

* column1: Any column expression.
* column2: Any column expression.
* string\_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_PART\_MILLIS(column1, string\_part \[, tz]) <a href="#datepartmilliscolumn1-stringpart--tz" id="datepartmilliscolumn1-stringpart--tz"></a>

Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.

* column1: Any column expression.
* string\_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day\_of\_year, day\_of\_week, iso\_week, iso\_year, iso\_dow, timezone, timezone\_hour, and timezone\_minute.
* tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.

#### DATE\_PART\_STR(column1, string\_part) <a href="#datepartstrcolumn1-stringpart" id="datepartstrcolumn1-stringpart"></a>

Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.

* column1: Any column expression.
* string\_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day\_of\_year, day\_of\_week, iso\_week, iso\_year, iso\_dow, timezone, timezone\_hour, and timezone\_minute.

#### DATE\_TRUNC\_MILLIS(column1, string\_part) <a href="#datetruncmilliscolumn1-stringpart" id="datetruncmilliscolumn1-stringpart"></a>

Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.

* column1: Any column expression.
* string\_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_TRUNC\_STR(column1, string\_part) <a href="#datetruncstrcolumn1-stringpart" id="datetruncstrcolumn1-stringpart"></a>

Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.

* column1: Any column expression.
* string\_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### MILLIS(column1) <a href="#milliscolumn1" id="milliscolumn1"></a>

Returns date that has been converted in a supported format to UNIX milliseconds.

* column1: Any column expression.

#### STR\_TO\_MILLIS(column1) <a href="#strtomilliscolumn1" id="strtomilliscolumn1"></a>

Returns date that has been converted in a supported format to UNIX milliseconds.

* column1: Any column expression.

#### MILLIS\_TO\_STR(column \[, string\_fmt]) <a href="#millistostrcolumn--stringfmt" id="millistostrcolumn--stringfmt"></a>

Returns the string in the supported format to which the UNIX milliseconds has been converted.

* column1: Any column expression.
* string\_fmt: The datetime format to return the system clock in.

#### MILLIS\_TO\_UTC(column \[, string\_fmt]) <a href="#millistoutccolumn--stringfmt" id="millistoutccolumn--stringfmt"></a>

Returns the UTC string to which the UNIX time stamp has been converted in the supported format.

* column1: Any column expression.
* string\_fmt: The datetime format to return the system clock in.

#### MILLIS\_TO\_TZ(column, string\_tzname \[, string\_fmt]) <a href="#millistotzcolumn-stringtzname--stringfmt" id="millistotzcolumn-stringtzname--stringfmt"></a>

Converts the UNIX time stamp to a string in the named time zone, and returns the string.

* column1: Any column expression.
* string\_tzname: The time zone name.
* string\_fmt: The datetime format to return the system clock in.

#### NOW\_MILLIS() <a href="#nowmillis" id="nowmillis"></a>

Returns statement time stamp as UNIX milliseconds; does not vary during a query.

#### NOW\_STR(\[string\_fmt]) <a href="#nowstrstringfmt" id="nowstrstringfmt"></a>

Returns statement time stamp as a string in a supported format; does not vary during a query.

* string\_fmt: The datetime format to return the timestamp in.

#### STR\_TO\_UTC(column1) <a href="#strtoutccolumn1" id="strtoutccolumn1"></a>

Converts the ISO 8601 time stamp to UTC.

* column1: Any column expression.

#### STR\_TO\_ZONE\_NAME(column, string\_tzname) <a href="#strtozonenamecolumn-stringtzname" id="strtozonenamecolumn-stringtzname"></a>

Converts the supported time stamp string to the named time zone.

* column1: Any column expression.
* string\_tzname: The time zone name.

#### BASE64(expression) <a href="#base64expression" id="base64expression"></a>

Returns base64 encoding of expression.

* expression: Any column or literal expression.

#### ABS(expression) <a href="#absexpression" id="absexpression"></a>

Returns absolute value of the number.

* expression: Any column or literal expression.

#### ACOS(expression) <a href="#acosexpression" id="acosexpression"></a>

Returns arccosine in radians.

* expression: Any column or literal expression.

#### ASIN(expression) <a href="#asinexpression" id="asinexpression"></a>

Returns arcsine in radians.

* expression: Any column or literal expression.

#### ATAN(expression) <a href="#atanexpression" id="atanexpression"></a>

Returns arctangent in radians.

* expression: Any column or literal expression.

#### ATAN2(expression1, expression2) <a href="#atan2expression1-expression2" id="atan2expression1-expression2"></a>

Returns arctangent of expression2/expression1.

* expression1: Any column or literal expression.
* expression2: Any column or literal expression.

#### CEIL(expression) <a href="#ceilexpression" id="ceilexpression"></a>

Returns smallest integer not less than the number.

* expression: Any column or literal expression.

#### COS(expression) <a href="#cosexpression" id="cosexpression"></a>

Returns cosine.

* expression: Any column or literal expression.

#### DEGREES(expression) <a href="#degreesexpression" id="degreesexpression"></a>

Returns radians to degrees.

* expression: Any column or literal expression.

#### E() <a href="#e" id="e"></a>

Base of natural logarithms.

#### EXP(expression) <a href="#expexpression" id="expexpression"></a>

Returns e^expression.

* expression: Any column or literal expression.

#### LN(expression) <a href="#lnexpression" id="lnexpression"></a>

Returns log base e.

* expression: Any column or literal expression.

#### LOG(expression) <a href="#logexpression" id="logexpression"></a>

Returns log base 10.

* expression: Any column or literal expression.

#### FLOOR(expression) <a href="#floorexpression" id="floorexpression"></a>

Largest integer not greater than the number.

* expression: Any column or literal expression.

#### PI() <a href="#pi" id="pi"></a>

Returns PI.

#### POWER(expression1, expression2) <a href="#powerexpression1-expression2" id="powerexpression1-expression2"></a>

Returns expression1^expression2.

* expression1: Any column or literal expression.
* expression2: Any column or literal expression.

#### RADIANS(expression) <a href="#radiansexpression" id="radiansexpression"></a>

Returns degrees to radians.

* expression: Any column or literal expression.

#### RANDOM(\[expression]) <a href="#randomexpression" id="randomexpression"></a>

Returns pseudo-random number with optional seed.

* expression: Any column or literal expression.

#### ROUND(expression \[, integer\_digits]) <a href="#roundexpression--integerdigits" id="roundexpression--integerdigits"></a>

Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

* expression: Any column or literal expression.
* integer\_digits: The number of digits to round to.

#### SIGN(expression) <a href="#signexpression" id="signexpression"></a>

Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.

* expression: Any column or literal expression.

#### SIN(expression) <a href="#sinexpression" id="sinexpression"></a>

Returns sine.

* expression: Any column or literal expression.

#### SQRT(expression) <a href="#sqrtexpression" id="sqrtexpression"></a>

Returns square root.

* expression: Any column or literal expression.

#### TAN(expression) <a href="#tanexpression" id="tanexpression"></a>

Returns tangent.

* expression: Any column or literal expression.

#### TRUNC(expression \[, integer\_digits]) <a href="#truncexpression--integerdigits" id="truncexpression--integerdigits"></a>

Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

* expression: Any column or literal expression.
* integer\_digits: The number of digits to truncate.

#### CONTAINS(column, string\_substring) <a href="#containscolumn-stringsubstring" id="containscolumn-stringsubstring"></a>

True if the string contains the substring.

* column: Any column or literal expression.
* string\_substring: The substring to search for.

#### INITCAP(column) <a href="#initcapcolumn" id="initcapcolumn"></a>

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

* column: Any column or literal expression.

#### LENGTH(column) <a href="#lengthcolumn" id="lengthcolumn"></a>

Returns length of the string value.

* column: Any column or literal expression.

#### LOWER(column) <a href="#lowercolumn" id="lowercolumn"></a>

Returns lowercase of the string value.

* column: Any column or literal expression.

#### LTRIM(column \[, string\_chars]) <a href="#ltrimcolumn--stringchars" id="ltrimcolumn--stringchars"></a>

Returns string with all leading chars removed. White space by default.

* column: Any column or literal expression.
* string\_chars: The leading characters to remove.

#### POSITION(column, string\_substring) <a href="#positioncolumn-stringsubstring" id="positioncolumn-stringsubstring"></a>

Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.

* column: Any column or literal expression.
* string\_substring: The substring to search for.

#### REPEAT(column, integer\_n) <a href="#repeatcolumn-integern" id="repeatcolumn-integern"></a>

Returns string formed by repeating expression n times.

* column: Any column or literal expression.
* integer\_n: The number of times to repeat column.

#### REPLACE(column, string\_substring, string\_replace \[, integer\_n]) <a href="#replacecolumn-stringsubstring-stringreplace--integern" id="replacecolumn-stringsubstring-stringreplace--integern"></a>

Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.

* column: The column expression.
* string\_substring: The regular expression to match.
* string\_replace: The value to replace the matched pattern.
* integer\_n: The maximum number of replacements to make.

#### RTRIM(column \[, string\_chars]) <a href="#rtrimcolumn--stringchars" id="rtrimcolumn--stringchars"></a>

Returns string with all trailing chars removed. White space by default.

* column: Any column or literal expression.
* string\_chars: The trailing characters to remove.

#### SPLIT(column \[, string\_sep]) <a href="#splitcolumn--stringsep" id="splitcolumn--stringsep"></a>

Splits the string into an array of substrings separated by string\_sep. If string\_sep is not given, any combination of white space characters is used.

* column: Any column or literal expression.
* string\_sep: The separator to split column on.

#### SUBSTR(column, integer\_position \[, integer\_length]) <a href="#substrcolumn-integerposition--integerlength" id="substrcolumn-integerposition--integerlength"></a>

Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.

* column: Any column or literal expression.
* integer\_position: The starting position.
* integer\_length: The total length of the substring to retrieve.

#### TRIM(column \[, string\_chars]) <a href="#trimcolumn--stringchars" id="trimcolumn--stringchars"></a>

Returns string with all leading and trailing chars removed. White space by default.

* column: Any column or literal expression.
* string\_chars: The leading and trailing characters to remove.

#### UPPER(column) <a href="#uppercolumn" id="uppercolumn"></a>

Returns uppercase of the string value.

* column: Any column or literal expression.

#### TOARRAY(column) <a href="#toarraycolumn" id="toarraycolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.

* column: Any column expression.

#### TOATOM(column) <a href="#toatomcolumn" id="toatomcolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.

* column: Any column expression.

#### TOBOOLEAN(column) <a href="#tobooleancolumn" id="tobooleancolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.

* column: Any column expression.

#### TONUMBER(column) <a href="#tonumbercolumn" id="tonumbercolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.

* column: Any column expression.

#### TOOBJECT(column) <a href="#toobjectcolumn" id="toobjectcolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.

* column: Any column expression.

#### TOSTRING(column) <a href="#tostringcolumn" id="tostringcolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.

* column: Any column expression.

### Predicate Functions <a href="#default" id="default"></a>

#### REGEXP\_CONTAINS(column, string\_pattern) <a href="#regexpcontainscolumn-stringpattern" id="regexpcontainscolumn-stringpattern"></a>

Returns True if the string value contains the regular expression pattern.

* column: The column expression.
* string\_pattern: The regular expression to match.

#### REGEXP\_LIKE(column, string\_pattern) <a href="#regexplikecolumn-stringpattern" id="regexplikecolumn-stringpattern"></a>

Returns True if the string value matches the regular expression pattern.

* column: The column expression.
* string\_pattern: The regular expression to match.

#### REGEXP\_POSITION(column, string\_pattern) <a href="#regexppositioncolumn-stringpattern" id="regexppositioncolumn-stringpattern"></a>

Returns first position of the regular expression pattern within the string, or -1.

* column: The column expression.
* string\_pattern: The regular expression to match.

#### REGEXP\_REPLACE(column, string\_pattern, string\_replace \[, integer\_n]) <a href="#regexpreplacecolumn-stringpattern-stringreplace--integern" id="regexpreplacecolumn-stringpattern-stringreplace--integern"></a>

Returns new string with occurrences of pattern replaced with string\_replace. If n is given, at most n replacements are performed.

* column: The column expression.
* string\_pattern: The regular expression to match.
* string\_replace: The value to replace the matched pattern.
* integer\_n: The maximum number of replacements to make.

#### ISARRAY(column) <a href="#isarraycolumn" id="isarraycolumn"></a>

Returns True if expression is an array, otherwise returns MISSING, NULL or false.

* column: Any column expression.

#### ISATOM(column) <a href="#isatomcolumn" id="isatomcolumn"></a>

Returns True if expression is a Boolean, number, or string, otherwise returns MISSING, NULL or false.

* column: Any column expression.

#### ISBOOLEAN(column) <a href="#isbooleancolumn" id="isbooleancolumn"></a>

Returns True if expression is a Boolean, otherwise returns MISSING, NULL or false.

* column: Any column expression.

#### ISNUMBER(column) <a href="#isnumbercolumn" id="isnumbercolumn"></a>

Returns True if expression is a number, otherwise returns MISSING, NULL or false.

* column: Any column expression.

#### ISOBJECT(column) <a href="#isobjectcolumn" id="isobjectcolumn"></a>

Returns True if expression is an object, otherwise returns MISSING, NULL or false.

* column: Any column expression.

#### ISSTRING(column) <a href="#isstringcolumn" id="isstringcolumn"></a>

Returns True if expression is a string, otherwise returns MISSING, NULL or false.

* column: Any column expression.

#### TYPE(column) <a href="#typecolumn" id="typecolumn"></a>

Returns one of the following strings, based on the value of expression: missing, null, boolean, number, string, array, object, or binary.

* column: Any column expression.

#### ARRAY\_AVG(column) <a href="#arrayavgcolumn" id="arrayavgcolumn"></a>

Returns arithmetic mean (average) of all the non-NULL number values in the array, or NULL if there are no such values.

* column: Any column expression.

#### ARRAY\_CONTAINS(column, value) <a href="#arraycontainscolumn-value" id="arraycontainscolumn-value"></a>

Returns true if the array contains value.

* column: Any column expression.
* value: The value contained within the array.

#### ARRAY\_COUNT(column) <a href="#arraycountcolumn" id="arraycountcolumn"></a>

Returns count of all the non-NULL values in the array, or zero if there are no such values.

* column: Any column expression.

#### ARRAY\_LENGTH(column) <a href="#arraylengthcolumn" id="arraylengthcolumn"></a>

Returns the number of elements in the array.

* column: Any column expression.

#### ARRAY\_MAX(column) <a href="#arraymaxcolumn" id="arraymaxcolumn"></a>

Returns the largest non-NULL, non-MISSING array element, in N1QL collation order.

* column: Any column expression.

#### ARRAY\_MIN(column) <a href="#arraymincolumn" id="arraymincolumn"></a>

Returns smallest non-NULL, non-MISSING array element, in N1QL collation order.

* column: Any column expression.

#### ARRAY\_POSITION(column, value) <a href="#arraypositioncolumn-value" id="arraypositioncolumn-value"></a>

Returns the first position of value within the array, or -1. Array position is zero-based, i.e. the first position is 0.

* column: Any column expression.
* value: The value contained within the array.

#### ARRAY\_SUM(column) <a href="#arraysumcolumn" id="arraysumcolumn"></a>

Sum of all the non-NULL number values in the array, or zero if there are no such values.

* column: Any column expression.

#### GREATEST(column1, column2 \[,column3 \[,column4]]) <a href="#greatestcolumn1-column2-column3-column4" id="greatestcolumn1-column2-column3-column4"></a>

Largest non-NULL, non-MISSING value if the values are of the same type; otherwise NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### LEAST(column1, column2 \[,column3 \[,column4]]) <a href="#leastcolumn1-column2-column3-column4" id="leastcolumn1-column2-column3-column4"></a>

Returns smallest non-NULL, non-MISSING value if the values are of the same type, otherwise returns NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFMISSING(column1, column2 \[,column3 \[,column4]]) <a href="#ifmissingcolumn1-column2-column3-column4" id="ifmissingcolumn1-column2-column3-column4"></a>

Returns the first non-MISSING value.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFMISSINGORNULL(column1, column2 \[,column3 \[,column4]]) <a href="#ifmissingornullcolumn1-column2-column3-column4" id="ifmissingornullcolumn1-column2-column3-column4"></a>

Returns first non-NULL, non-MISSING value.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFNULL(column1, column2 \[,column3 \[,column4]]) <a href="#ifnullcolumn1-column2-column3-column4" id="ifnullcolumn1-column2-column3-column4"></a>

Returns first non-NULL value. Note that this function might return MISSING if there is no non-NULL value.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### MISSINGIF(column1, column2) <a href="#missingifcolumn1-column2" id="missingifcolumn1-column2"></a>

Returns MISSING if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.

#### NULLIF(column1, column2) <a href="#nullifcolumn1-column2" id="nullifcolumn1-column2"></a>

Returns NULL if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.

#### IFINF(column1, column2 \[,column3 \[,column4]]) <a href="#ifinfcolumn1-column2-column3-column4" id="ifinfcolumn1-column2-column3-column4"></a>

Returns first non-MISSING, non-Inf number. Returns MISSING or NULL if a non-number input is encountered first.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFNAN(column1, column2 \[,column3 \[,column4]]) <a href="#ifnancolumn1-column2-column3-column4" id="ifnancolumn1-column2-column3-column4"></a>

Returns first non-MISSING, non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### IFNANORINF(column1, column2 \[,column3 \[,column4]]) <a href="#ifnanorinfcolumn1-column2-column3-column4" id="ifnanorinfcolumn1-column2-column3-column4"></a>

Returns first non-MISSING, non-Inf, or non-NaN number. Returns MISSING or NULL if a non-number input is encountered first.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### NANIF(column1, column2 \[,column3 \[,column4]]) <a href="#nanifcolumn1-column2-column3-column4" id="nanifcolumn1-column2-column3-column4"></a>

Returns NaN if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### NEGINFIF(column1, column2 \[,column3 \[,column4]]) <a href="#neginfifcolumn1-column2-column3-column4" id="neginfifcolumn1-column2-column3-column4"></a>

Returns NegInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### POSINFIF(column1, column2 \[,column3 \[,column4]]) <a href="#posinfifcolumn1-column2-column3-column4" id="posinfifcolumn1-column2-column3-column4"></a>

Returns PosInf if column1 = column2, otherwise returns column1. Returns MISSING or NULL if either input is MISSING or NULL.

* column1: Any column expression.
* column2: Any column expression.
* column3: Any column expression.
* column4: Any column expression.

#### CLOCK\_MILLIS() <a href="#clockmillis" id="clockmillis"></a>

Returns system clock at function evaluation time, as UNIX milliseconds. Varies during a query.

#### CLOCK\_STR(\[string\_fmt]) <a href="#clockstrstringfmt" id="clockstrstringfmt"></a>

Returns system clock at function evaluation time, as a string in a supported format. Varies during a query.

* string\_fmt: The datetime format to return the system clock in.

#### DATE\_ADD\_MILLIS(column, integer\_n, string\_part) <a href="#dateaddmilliscolumn-integern-stringpart" id="dateaddmilliscolumn-integern-stringpart"></a>

Performs date arithmetic, and returns result of computation. n and part are used to define an interval or duration, which is then added (or subtracted) to the UNIX time stamp, returning the result.

* column: Any column expression.
* integer\_n: The number of string\_part's to add to the column value.
* string\_part: The part to add integer\_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_ADD\_STR(column, integer\_n, string\_part) <a href="#dateaddstrcolumn-integern-stringpart" id="dateaddstrcolumn-integern-stringpart"></a>

Performs date arithmetic. n and part are used to define an interval or duration, which is then added (or subtracted) to the date string in a supported format, returning the result.

* column: Any column expression.
* integer\_n: The number of string\_part's to add to the column value.
* string\_part: The part to add integer\_n to, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_DIFF\_MILLIS(column1, column2, string\_part) <a href="#datediffmilliscolumn1-column2-stringpart" id="datediffmilliscolumn1-column2-stringpart"></a>

Performs date arithmetic. Returns the elapsed time between two UNIX time stamps as an integer whose unit is part.

* column1: Any column expression.
* column2: Any column expression.
* string\_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_DIFF\_STR(column1, column2, string\_part) <a href="#datediffstrcolumn1-column2-stringpart" id="datediffstrcolumn1-column2-stringpart"></a>

Performs date arithmetic. Returns the elapsed time between two date strings in a supported format, as an integer whose unit is part.

* column1: Any column expression.
* column2: Any column expression.
* string\_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_PART\_MILLIS(column1, string\_part \[, tz]) <a href="#datepartmilliscolumn1-stringpart--tz" id="datepartmilliscolumn1-stringpart--tz"></a>

Returns date part as an integer. The date expression is a number representing UNIX milliseconds, and part is one of the following date part strings.

* column1: Any column expression.
* string\_part: The component of the date to extract. Available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day\_of\_year, day\_of\_week, iso\_week, iso\_year, iso\_dow, timezone, timezone\_hour, and timezone\_minute.
* tz: The timezone to convert the local time to. Default to the system timezone if not specified. If an incorrect time zone is provided, the null is returned.

#### DATE\_PART\_STR(column1, string\_part) <a href="#datepartstrcolumn1-stringpart" id="datepartstrcolumn1-stringpart"></a>

Returns date part as an integer. The date expression is a string in a supported format, and part is one of the supported date part strings.

* column1: Any column expression.
* string\_part: The unit of the result, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, day\_of\_year, day\_of\_week, iso\_week, iso\_year, iso\_dow, timezone, timezone\_hour, and timezone\_minute.

#### DATE\_TRUNC\_MILLIS(column1, string\_part) <a href="#datetruncmilliscolumn1-stringpart" id="datetruncmilliscolumn1-stringpart"></a>

Returns UNIX time stamp that has been truncated so that the given date part string is the least significant.

* column1: Any column expression.
* string\_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### DATE\_TRUNC\_STR(column1, string\_part) <a href="#datetruncstrcolumn1-stringpart" id="datetruncstrcolumn1-stringpart"></a>

Returns ISO 8601 time stamp that has been truncated so that the given date part string is the least significant.

* column1: Any column expression.
* string\_part: The least significant date part, available values are: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, and millisecond.

#### MILLIS(column1) <a href="#milliscolumn1" id="milliscolumn1"></a>

Returns date that has been converted in a supported format to UNIX milliseconds.

* column1: Any column expression.

#### STR\_TO\_MILLIS(column1) <a href="#strtomilliscolumn1" id="strtomilliscolumn1"></a>

Returns date that has been converted in a supported format to UNIX milliseconds.

* column1: Any column expression.

#### MILLIS\_TO\_STR(column \[, string\_fmt]) <a href="#millistostrcolumn--stringfmt" id="millistostrcolumn--stringfmt"></a>

Returns the string in the supported format to which the UNIX milliseconds has been converted.

* column1: Any column expression.
* string\_fmt: The datetime format to return the system clock in.

#### MILLIS\_TO\_UTC(column \[, string\_fmt]) <a href="#millistoutccolumn--stringfmt" id="millistoutccolumn--stringfmt"></a>

Returns the UTC string to which the UNIX time stamp has been converted in the supported format.

* column1: Any column expression.
* string\_fmt: The datetime format to return the system clock in.

#### MILLIS\_TO\_TZ(column, string\_tzname \[, string\_fmt]) <a href="#millistotzcolumn-stringtzname--stringfmt" id="millistotzcolumn-stringtzname--stringfmt"></a>

Converts the UNIX time stamp to a string in the named time zone, and returns the string.

* column1: Any column expression.
* string\_tzname: The time zone name.
* string\_fmt: The datetime format to return the system clock in.

#### NOW\_MILLIS() <a href="#nowmillis" id="nowmillis"></a>

Returns statement time stamp as UNIX milliseconds; does not vary during a query.

#### NOW\_STR(\[string\_fmt]) <a href="#nowstrstringfmt" id="nowstrstringfmt"></a>

Returns statement time stamp as a string in a supported format; does not vary during a query.

* string\_fmt: The datetime format to return the timestamp in.

#### STR\_TO\_UTC(column1) <a href="#strtoutccolumn1" id="strtoutccolumn1"></a>

Converts the ISO 8601 time stamp to UTC.

* column1: Any column expression.

#### STR\_TO\_ZONE\_NAME(column, string\_tzname) <a href="#strtozonenamecolumn-stringtzname" id="strtozonenamecolumn-stringtzname"></a>

Converts the supported time stamp string to the named time zone.

* column1: Any column expression.
* string\_tzname: The time zone name.

#### BASE64(expression) <a href="#base64expression" id="base64expression"></a>

Returns base64 encoding of expression.

* expression: Any column or literal expression.

#### ABS(expression) <a href="#absexpression" id="absexpression"></a>

Returns absolute value of the number.

* expression: Any column or literal expression.

#### ACOS(expression) <a href="#acosexpression" id="acosexpression"></a>

Returns arccosine in radians.

* expression: Any column or literal expression.

#### ASIN(expression) <a href="#asinexpression" id="asinexpression"></a>

Returns arcsine in radians.

* expression: Any column or literal expression.

#### ATAN(expression) <a href="#atanexpression" id="atanexpression"></a>

Returns arctangent in radians.

* expression: Any column or literal expression.

#### ATAN2(expression1, expression2) <a href="#atan2expression1-expression2" id="atan2expression1-expression2"></a>

Returns arctangent of expression2/expression1.

* expression1: Any column or literal expression.
* expression2: Any column or literal expression.

#### CEIL(expression) <a href="#ceilexpression" id="ceilexpression"></a>

Returns smallest integer not less than the number.

* expression: Any column or literal expression.

#### COS(expression) <a href="#cosexpression" id="cosexpression"></a>

Returns cosine.

* expression: Any column or literal expression.

#### DEGREES(expression) <a href="#degreesexpression" id="degreesexpression"></a>

Returns radians to degrees.

* expression: Any column or literal expression.

#### E() <a href="#e" id="e"></a>

Base of natural logarithms.

#### EXP(expression) <a href="#expexpression" id="expexpression"></a>

Returns e^expression.

* expression: Any column or literal expression.

#### LN(expression) <a href="#lnexpression" id="lnexpression"></a>

Returns log base e.

* expression: Any column or literal expression.

#### LOG(expression) <a href="#logexpression" id="logexpression"></a>

Returns log base 10.

* expression: Any column or literal expression.

#### FLOOR(expression) <a href="#floorexpression" id="floorexpression"></a>

Largest integer not greater than the number.

* expression: Any column or literal expression.

#### PI() <a href="#pi" id="pi"></a>

Returns PI.

#### POWER(expression1, expression2) <a href="#powerexpression1-expression2" id="powerexpression1-expression2"></a>

Returns expression1^expression2.

* expression1: Any column or literal expression.
* expression2: Any column or literal expression.

#### RADIANS(expression) <a href="#radiansexpression" id="radiansexpression"></a>

Returns degrees to radians.

* expression: Any column or literal expression.

#### RANDOM(\[expression]) <a href="#randomexpression" id="randomexpression"></a>

Returns pseudo-random number with optional seed.

* expression: Any column or literal expression.

#### ROUND(expression \[, integer\_digits]) <a href="#roundexpression--integerdigits" id="roundexpression--integerdigits"></a>

Rounds the value to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

* expression: Any column or literal expression.
* integer\_digits: The number of digits to round to.

#### SIGN(expression) <a href="#signexpression" id="signexpression"></a>

Valid values: -1, 0, or 1 for negative, zero, or positive numbers respectively.

* expression: Any column or literal expression.

#### SIN(expression) <a href="#sinexpression" id="sinexpression"></a>

Returns sine.

* expression: Any column or literal expression.

#### SQRT(expression) <a href="#sqrtexpression" id="sqrtexpression"></a>

Returns square root.

* expression: Any column or literal expression.

#### TAN(expression) <a href="#tanexpression" id="tanexpression"></a>

Returns tangent.

* expression: Any column or literal expression.

#### TRUNC(expression \[, integer\_digits]) <a href="#truncexpression--integerdigits" id="truncexpression--integerdigits"></a>

Truncates the number to the given number of integer digits to the right of the decimal point (left if digits is negative). Digits is 0 if not given.

* expression: Any column or literal expression.
* integer\_digits: The number of digits to truncate.

#### CONTAINS(column, string\_substring) <a href="#containscolumn-stringsubstring" id="containscolumn-stringsubstring"></a>

True if the string contains the substring.

* column: Any column or literal expression.
* string\_substring: The substring to search for.

#### INITCAP(column) <a href="#initcapcolumn" id="initcapcolumn"></a>

Converts the string so that the first letter of each word is uppercase and every other letter is lowercase.

* column: Any column or literal expression.

#### LENGTH(column) <a href="#lengthcolumn" id="lengthcolumn"></a>

Returns length of the string value.

* column: Any column or literal expression.

#### LOWER(column) <a href="#lowercolumn" id="lowercolumn"></a>

Returns lowercase of the string value.

* column: Any column or literal expression.

#### LTRIM(column \[, string\_chars]) <a href="#ltrimcolumn--stringchars" id="ltrimcolumn--stringchars"></a>

Returns string with all leading chars removed. White space by default.

* column: Any column or literal expression.
* string\_chars: The leading characters to remove.

#### POSITION(column, string\_substring) <a href="#positioncolumn-stringsubstring" id="positioncolumn-stringsubstring"></a>

Returns the first position of the substring within the string, or -1. The position is zero-based, i.e., the first position is 0.

* column: Any column or literal expression.
* string\_substring: The substring to search for.

#### REPEAT(column, integer\_n) <a href="#repeatcolumn-integern" id="repeatcolumn-integern"></a>

Returns string formed by repeating expression n times.

* column: Any column or literal expression.
* integer\_n: The number of times to repeat column.

#### REPLACE(column, string\_substring, string\_replace \[, integer\_n]) <a href="#replacecolumn-stringsubstring-stringreplace--integern" id="replacecolumn-stringsubstring-stringreplace--integern"></a>

Returns string with all occurrences of substr replaced with repl. If n is given, at most n replacements are performed.

* column: The column expression.
* string\_substring: The regular expression to match.
* string\_replace: The value to replace the matched pattern.
* integer\_n: The maximum number of replacements to make.

#### RTRIM(column \[, string\_chars]) <a href="#rtrimcolumn--stringchars" id="rtrimcolumn--stringchars"></a>

Returns string with all trailing chars removed. White space by default.

* column: Any column or literal expression.
* string\_chars: The trailing characters to remove.

#### SPLIT(column \[, string\_sep]) <a href="#splitcolumn--stringsep" id="splitcolumn--stringsep"></a>

Splits the string into an array of substrings separated by string\_sep. If string\_sep is not given, any combination of white space characters is used.

* column: Any column or literal expression.
* string\_sep: The separator to split column on.

#### SUBSTR(column, integer\_position \[, integer\_length]) <a href="#substrcolumn-integerposition--integerlength" id="substrcolumn-integerposition--integerlength"></a>

Returns substring from the integer position of the given length, or to the end of the string. The position is zero-based, i.e. the first position is 0. If position is negative, it is counted from the end of the string; -1 is the last position in the string.

* column: Any column or literal expression.
* integer\_position: The starting position.
* integer\_length: The total length of the substring to retrieve.

#### TRIM(column \[, string\_chars]) <a href="#trimcolumn--stringchars" id="trimcolumn--stringchars"></a>

Returns string with all leading and trailing chars removed. White space by default.

* column: Any column or literal expression.
* string\_chars: The leading and trailing characters to remove.

#### UPPER(column) <a href="#uppercolumn" id="uppercolumn"></a>

Returns uppercase of the string value.

* column: Any column or literal expression.

#### TOARRAY(column) <a href="#toarraycolumn" id="toarraycolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays are themselves; All other values are wrapped in an array.

* column: Any column expression.

#### TOATOM(column) <a href="#toatomcolumn" id="toatomcolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; Arrays of length 1 are the result of TOATOM() on their single element; Objects of length 1 are the result of TOATOM() on their single value; Booleans, numbers, and strings are themselves; All other values are NULL.

* column: Any column expression.

#### TOBOOLEAN(column) <a href="#tobooleancolumn" id="tobooleancolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; False is false; Numbers +0, -0, and NaN are false; Empty strings, arrays, and objects are false; All other values are true.

* column: Any column expression.

#### TONUMBER(column) <a href="#tonumbercolumn" id="tonumbercolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; False is 0; True is 1; Numbers are themselves; Strings that parse as numbers are those numbers; All other values are NULL.

* column: Any column expression.

#### TOOBJECT(column) <a href="#toobjectcolumn" id="toobjectcolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; Objects are themselves; All other values are the empty object.

* column: Any column expression.

#### TOSTRING(column) <a href="#tostringcolumn" id="tostringcolumn"></a>

Returns array as follows: MISSING is MISSING; NULL is NULL; False is "false"; True is "true"; Numbers are their string representation; Strings are themselves; All other values are NULL.

* column: Any column expression.