Snowflake

SELECT Statements

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:

SELECT {

[ TOP <numeric_literal> | DISTINCT ]

{

*

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

<expression>

]

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

  1. Return all columns:

    SELECT * FROM [DemoDB].[PUBLIC].Products

  2. Rename a column:

    SELECT [ProductName] AS MY_ProductName FROM [DemoDB].[PUBLIC].Products

  3. Cast a column's data as a different data type:

    SELECT CAST(Price AS VARCHAR) AS Str_Price FROM [DemoDB].[PUBLIC].Products

  4. Search data:

    SELECT * FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'

  5. Return the number of items matching the query criteria:

    SELECT COUNT(*) AS MyCount FROM [DemoDB].[PUBLIC].Products

  6. Return the number of unique items matching the query criteria:

    SELECT COUNT(DISTINCT ProductName) FROM [DemoDB].[PUBLIC].Products

  7. Return the unique items matching the query criteria:

    SELECT DISTINCT ProductName FROM [DemoDB].[PUBLIC].Products

  8. Summarize data:

    SELECT ProductName, MAX(Price) FROM [DemoDB].[PUBLIC].Products GROUP BY ProductName

    See Aggregate Functions below for details.

  9. Retrieve data from multiple tables.

    SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId

    See JOIN Queries below for details.

  10. Sort a result set in ascending order:

    SELECT Id, ProductName FROM [DemoDB].[PUBLIC].Products ORDER BY ProductName ASC

  11. Restrict a result set to the specified number of rows:

    SELECT Id, ProductName FROM [DemoDB].[PUBLIC].Products LIMIT 10

  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM [DemoDB].[PUBLIC].Products WHERE ProductName = @param

Aggregate Functions

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'

COUNT(DISTINCT)

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

SELECT COUNT(DISTINCT Id) AS DistinctValues FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'

AVG

Returns the average of the column values.

SELECT ProductName, AVG(Price) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu' GROUP BY ProductName

MIN

Returns the minimum column value.

SELECT MIN(Price), ProductName FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu' GROUP BY ProductName

MAX

Returns the maximum column value.

SELECT ProductName, MAX(Price) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu' GROUP BY ProductName

SUM

Returns the total sum of the column values.

SELECT SUM(Price) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'

JOIN Queries

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

Inner Join

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

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:

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

Date Literal Functions

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()

The current day.

SELECT * FROM MyTable WHERE MyDateField = L_TODAY()

L_YESTERDAY()

The previous day.

SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()

L_TOMORROW()

The following day.

SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()

L_LAST_WEEK()

Every day in the preceding week.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()

L_THIS_WEEK()

Every day in the current week.

SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()

L_NEXT_WEEK()

Every day in the following week.

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)

The previous n days, excluding the current day.

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)

L_NEXT_N_DAYS(n)

The following n days, including the current day.

SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)

Also available:

  • L_LAST/L_NEXT_90_DAYS

L_LAST_N_WEEKS(n)

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

SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)

L_NEXT_N_WEEKS(n)

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

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

BITAND(expr1, expr2)

Bitwise AND of two numeric expressions (a and b).

  • expr1: This expression must evaluate to a data type that can be cast to INTEGER.

  • expr2: This expression must evaluate to a data type that can be cast to INTEGER.

BITNOT(expr)

Bitwise negation of a numeric expression.

  • expr: This expression must evaluate to a data type that can be cast to INTEGER.

BITOR(expr1, expr2)

Bitwise OR of two numeric expressions (a and b).

  • expr1: This expression must evaluate to a data type that can be cast to INTEGER.

  • expr2: This expression must evaluate to a data type that can be cast to INTEGER.

BITSHIFTLEFT(expr1, n)

Shift the bits for a numeric expression n positions to the left.

  • expr1:

  • n:

BITSHIFTRIGHT(expr1, n)

Shift the bits for a numeric expression n positions to the right, with sign extension.

  • expr1: This expression must evaluate to a data type that can be cast to INTEGER.

  • n: The number of bits to shift by.

BITXOR(expr1, expr2)

Bitwise XOR of two numeric expressions (a and b).

  • expr1:

  • expr2:

BOOLAND(expr1, expr2)

Computes the Boolean AND of two numeric expressions. In accordance with Boolean semantics. Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.

  • expr1:

  • expr2:

BOOLNOT(expr1)

Computes the Boolean NOT of a single numeric expression. In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.

  • expr1:

BOOLOR(expr1, expr2)

Computes the Boolean OR of two numeric expressions. In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.

  • expr1:

  • expr2:

BOOLXOR(expr1, expr2)

Computes the Boolean XOR of two numeric expressions (i.e. one of the expressions, but not both expressions, is TRUE). In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.

  • expr1:

  • expr2:

COALESCE(expr1 [, ...])

Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.

  • expr1: Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.

DECODE(expr, search1, result1 [, search2, result2, ...] [, default ] )

Compares the select expression to each search expression in order. As soon as a search expression matches the selection expression, the corresponding result expression is returned.

  • expr: This is the "select expression". The "search expressions" are compared to this select expression, and if there is a match then DECODE returns the result that corresponds to that search expression. The select expression is typically a column, but can be a subquery, literal, or other expression.

  • search1: The search expressions indicate the values to compare to the select expression. If one of these search expressions matches, the function returns the corresponding result. If more than one search expression would match, only the first match's result is returned.

  • result1: The results are the values that will be returned if one of the search expressions matches the select expression.

  • search2: The search expressions indicate the values to compare to the select expression. If one of these search expressions matches, the function returns the corresponding result. If more than one search expression would match, only the first match's result is returned.

  • result2: The results are the values that will be returned if one of the search expressions matches the select expression.

  • default: If an optional default is specified, and if none of the search expressions match the select expression, then DECODE returns this default value.

EQUAL_NULL(expr1, expr2)

Compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator (=), which treats NULLs as unknown values.

  • expr1:

  • expr2:

GREATEST(expr [, ...])

Returns the largest value from a list of expressions. GREATEST supports all types, including VARIANT. The first argument determines the return type. If the first type is numeric, then the return type will be 'widened' according to the numeric types in the list of all arguments. If the first type is not numeric, then all other arguments must be convertible to the first type. If any of the argument values is NULL, the result will be NULL.

  • expr:

IFF(condition, expr1, expr2)

Single-level if-then-else expression. Similar to CASE, but only allows a single condition. If condition evaluates to TRUE, returns expr1, otherwise returns expr2.

  • condition: The condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL).

  • expr1: A general expression. This value is returned if the condition is true.

  • expr2: A general expression. This value is returned if the condition is false.

IFNULL(expr1, expr2)

If expr1 is NULL, returns expr2, otherwise returns expr1.

  • expr1: A general expression.

  • expr2: A general expression.

LEAST(expr [, ...])

Returns the smallest value from a list of expressions. LEAST supports all data types, including VARIANT.

  • expr: The arguments must include at least one expression. All the expressions should be of the same type or compatible types.

NULLIF(1, 2)

Returns NULL if expr1 is equal to expr2, otherwise returns expr1.

  • expr1: any expression

  • expr2: any expression

NVL(expr1, expr2)

If expr1 is NULL, returns expr2, otherwise returns expr1.

  • expr1: The expression to be checked to see whether it's NULL.

  • expr2: If expr1 is NULL, this expression will be evaluated and its value will be returned.

NVL2(expr1, expr2, expr3)

Returns values depending on the nullness of the first argument: If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

  • expr1: The expression to be checked to see whether it's NULL.

  • expr2: If expr1 is not NULL, this expression will be evaluated and its value will be returned.

  • expr3: If expr1 is NULL, this expression will be evaluated and its value will be returned.

REGR_VALX(expr1, expr2)

If the first argument is NULL, returns NULL. Otherwise, returns the second argument. Contrast REGR_VALX and REGR_VALY with NVL: NVL is a NULL-replacing function. The less commonly used REGR_VALX and REGR_VALY are NULL-preserving functions.

  • expr1:

  • expr2:

REGR_VALY(expr1, expr2)

If the second argument is NULL, returns NULL; otherwise, returns the first argument. Contrast REGR_VALX and REGR_VALY with NVL: NVL is a NULL-replacing function. The less commonly used REGR_VALX and REGR_VALY are NULL-preserving functions.

  • expr1:

  • expr2:

ZEROIFNULL(expr)

Returns 0 if its argument is null; otherwise, returns its argument.

  • expr:

CURRENT_CLIENT()

Returns the version of the client from which the function was called. If called from an application using the JDBC or ODBC driver to connect to Snowflake, returns the version of the driver.

CURRENT_DATE()

Returns the current date of the system.

CURRENT_TIME([fract_sec_precision])

Returns the current time for the system.

  • fract_sec_precision>: This optional argument indicates the precision with which to report the time. For example, a value of 3 says to use 3 digits after the decimal point - i.e. to specify the time with a precision of milliseconds.

CURRENT_TIMESTAMP([fract_sec_precision])

Returns the current timestamp for the system.

  • fract_sec_precision: This optional argument indicates the precision with which to report the time. For example, a value of 3 says to use 3 digits after the decimal point - i.e. to specify the time with a precision of milliseconds.

CURRENT_VERSION()

Returns the current Snowflake version.

LOCALTIME()

Returns the current time for the system. ANSI-compliant alias for CURRENT_TIME.

LOCALTIMESTAMP()

Returns the current timestamp for the system. ANSI-compliant alias for CURRENT_TIMESTAMP.

CURRENT_ROLE()

Returns the name of the role in use for the current session. To specify a different role for the session, execute the USE ROLE command.

CURRENT_SESSION()

Returns a unique system identifier for the Snowflake session corresponding to the present connection. This will generally be a system-generated alphanumeric string. It is NOT derived from the user name or user account.

CURRENT_STATEMENT()

Returns the SQL text of the statement that is currently executing.

CURRENT_TRANSACTION()

Returns the transaction id of an open transaction in the current session.

CURRENT_USER()

Returns the name of the user currently logged into the system.

LAST_QUERY_ID([num])

Returns the ID of a specified query in the current session. If no query is specified, the most recently-executed query is returned.

  • num: Specifies the query to return, based on the position of the query (within the session).

LAST_TRANSACTION()

Returns the transaction ID of the last transaction that was either committed or rolled back in the current session.

CURRENT_DATABASE()

Returns the name of the database in use for the current session. To specify a different database for the session, execute the USE DATABASE command.

CURRENT_SCHEMA()

Returns the name of the schema in use by the current session. To specify a different schema for the session, execute the USE SCHEMA command.

CURRENT_SCHEMAS()

Returns active search path schemas. For more information about search path, see Object Name Resolution.

CURRENT_WAREHOUSE()

Returns the name of the warehouse in use for the current session. To specify a different warehouse for the session, execute the USE WAREHOUSE command.

CAST(source_expr AS target_data_type)

Converts a value of one data type into another data type. The semantics of CAST are the same as the semantics of the corresponding TO_ datatype conversion functions. If the cast is not possible, an error is raised. For more details, see the individual TO_ datatype conversion functions.

  • source_expr: Expression of any supported data type to be converted into a different data type.

  • target_data_type: The data type to which to convert the expression. If the data type supports additional properties, such as scale and precision (for numbers/decimals), the properties can be included.

TRY_CAST(1 AS 2)

A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.

  • source_expr: Expression of any supported data type to be converted into a different data type.

  • target_data_type: The data type to which to convert the expression. If the data type supports additional properties, such as scale and precision (for numbers/decimals), the properties can be included.

TO_CHAR(expr [, format])

Converts the input expression to a string. For NULL input, the output is NULL.

  • expr: An expression of any data type.

  • format: The format of the output string

TO_VARCHAR(expr [, format])

Converts the input expression to a string. For NULL input, the output is NULL.

  • expr: An expression of any data type.

  • format: The format of the output string

TO_BINARY(string_expr [, format])

Converts the input expression to a binary value. For NULL input, the output is NULL.

  • string_expr: A string expression.

  • format: The binary format for conversion: HEX, BASE64, or UTF-8 (see Binary Input and Output). The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default is HEX

TRY_TO_BINARY(string_expr [, format])

A special version of TO_BINARY that performs the same operation (i.e. converts an input expression to a binary value), but with error handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • string_expr: A string expression.

  • format: The binary format for conversion: HEX, BASE64, or UTF-8 (see Binary Input and Output). The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default is HEX

TO_DECIMAL(expr [, format [, precision [, scale]]])

Converts an input expression to a fixed-point number. For NULL input, the output is NULL.

  • expr: An expression of a numeric, character, or variant type.

  • format: The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.

  • precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).

  • scale: The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number).

TRY_TO_DECIMAL(expr [, format [, precision [, scale]]])

A special version of TO_DECIMAL, TO_NUMBER, TO_NUMERIC that performs the same operation (i.e. converts an input expression to a fixed-point number), but with error-handling support (i.e if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: An expression of a numeric, character, or variant type.

  • format: The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.

  • precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).

  • scale: The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number).

TO_DOUBLE(expr [, format])

Converts an expression to a double-precision floating-point number.

  • expr: An expression of a numeric, character, or variant type.

  • format: If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.

TRY_TO_DOUBLE(expr [, format])

A special version of TO_DOUBLE that performs the same operation (i.e. converts an input expression to a double-precision floating-point number), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: An expression of a numeric, character, or variant type.

  • format: If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.

TO_BOOLEAN(text_or_numeric_expr)

Coverts the input text or numeric expression to a Boolean value. For NULL input, the output is NULL.

  • text_or_numeric_expr: A text or numeric expression

TRY_TO_BOOLEAN(text_or_numeric_expr)

A special version of TO_BOOLEAN that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • text_or_numeric_expr: A text or numeric expression

TO_DATE(expr [, format])

Converts an input expression to a date

  • expr: Expression to be converted into a date.

  • format: Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TRY_TO_DATE(expr [, format])

A special version of TO_DATE that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: Expression to be converted into a date.

  • format: Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TO_TIME(expr [, format])

Converts an input expression into a time. If input is NULL, returns NULL.

  • expr: Expression to be converted into a time

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TRY_TO_TIME(expr [, format])

A special version of TO_TIME that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: Expression to be converted into a time

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TO_TIMESTAMP(expr [, format])

Converts an input expression into the corresponding timestamp

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TRY_TO_TIMESTAMP(expr [, format])

A special version of TO_TIMESTAMP that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TO_TIMESTAMP_NTZ(expr [, format])

Converts an input expression into the corresponding timestamp

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TRY_TO_TIMESTAMP_NTZ(expr [, format])

A special version of TO_TIMESTAMP_NTZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TO_TIMESTAMP_TZ(expr [, format])

Converts an input expression into the corresponding timestamp

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TRY_TO_TIMESTAMP_TZ(expr [, format])

A special version of TO_TIMESTAMP_TZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TO_TIMESTAMP_LTZ(expr [, format])

Converts an input expression into the corresponding timestamp

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

TRY_TO_TIMESTAMP_LTZ(expr [, format])

A special version of TO_TIMESTAMP_LTZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

  • expr: Expression to be converted into a timestamp

  • format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.

RANDOM(seed)

Each call returns a pseudo-random 64-bit integer.

  • seed: Seed is an integer. Different seeds will cause RANDOM to produce different output values.

RANDSTR(length, gen)

Returns a random string of specified length. Individual characters are chosen uniformly at random from the following pool of characters: 0-9, a-z, A-Z.

  • length: Length of the string to generate

  • gen: The value for the generator expression, gen, is used as the seed for this uniform random distribution

UUID_STRING(uuid, name)

Generates either a version 4 (random) or version 5 (named) RFC 4122-compliant UUID as a formatted string.

  • uuid: The string (known as the namespace)

  • name: The name of the UUID

NORMAL(mean, stddev, gen)

Returns a normal-distributed floating point number, with specified mean and stddev (standard deviation).

  • mean: This is the value that you would like the output values centered around

  • stddev: This specifies the width of one standard deviation.

  • gen: This specifies the generator expression for the function.

UNIFORM(min, max, gen)

Returns a uniformly random number, in the inclusive range [min, max].

  • min: The Minimum Number

  • max: The Maximum Number

  • gen: The generator expression for the function

ZIPF(s, N, gen)

Returns a Zipf-distributed integer, for N elements and characteristic exponent s

  • s: the characteristic exponent

  • N: the number of elements

  • gen: The generator expression for the function

SEQ1([sign])

Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1 byte).

  • sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.

SEQ2([sign])

Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (2 byte).

  • sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.

SEQ4([sign])

Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (4 byte).

  • sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.

SEQ8([sign])

Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (8 byte).

  • sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.

DATE_FROM_PARTS(year, month, day)

Creates a date from individual numeric components that represent the year, month, and day of the month

  • year: The integer expression to use as a year for building a date.

  • month: The integer expression to use as a month for building a date, with January represented as 1, and December as 12.

  • day: The integer expression to use as a day for building a date, usually in the 1-31 range.

TIME_FROM_PARTS(hour, minute, second, nanoseconds)

Creates a time from individual numeric components.

  • hour: An integer expression to use as an hour for building a time, usually in the 0-23 range.

  • minute: An integer expression to use as a minute for building a time, usually in the 0-59 range.

  • second: An integer expression to use as a second for building a time, usually in the 0-59 range.

  • nanoseconds: A 9-digit integer expression to use as a nanosecond for building a time

TIMESTAMP_FROM_PARTS(date_expr, time_expr)

Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.

  • date_expr: provides the year, month, and day for the timestamp

  • time_expr: provides the hour, minute, second, and nanoseconds within the day

TIMESTAMP_FROM_PARTS(year, month, day, hour, minute, second [, nanoseconds [, time_zone]])

Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.

  • year: An integer expression to use as a year for building a timestamp.

  • month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.

  • day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.

  • hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.

  • minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.

  • second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.

  • nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.

  • time_zone: A string expression to use as a time zone for building a timestamp (e.g. America/Los_Angeles)

TIMESTAMP_NTZ_FROM_PARTS(date_expr, time_expr)

Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.

  • date_expr: provides the year, month, and day for the timestamp

  • time_expr: provides the hour, minute, second, and nanoseconds within the day

TIMESTAMP_NTZ_FROM_PARTS(year, month, day, hour, minute, second [, nanoseconds])

Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.

  • year: An integer expression to use as a year for building a timestamp.

  • month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.

  • day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.

  • hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.

  • minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.

  • second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.

  • nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.

TIMESTAMP_LTZ_FROM_PARTS(year, month, day, hour, minute, second [, nanoseconds])

Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.

  • year: An integer expression to use as a year for building a timestamp.

  • month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.

  • day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.

  • hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.

  • minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.

  • second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.

  • nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.

TIMESTAMP_TZ_FROM_PARTS(year, month, day, hour, minute, second [, nanoseconds [, time_zone]])

Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.

  • year: An integer expression to use as a year for building a timestamp.

  • month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.

  • day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.

  • hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.

  • minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.

  • second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.

  • nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.

  • time_zone: A string expression to use as a time zone for building a timestamp (e.g. America/Los_Angeles)

DATE_PART(date_or_time_part, date_or_time_expr)

Extracts the specified date or time part from a date, time, or timestamp.

  • date_or_time_part: The date or time part to extract

  • date_or_time_expr: The date or time expression to extract from

DAYNAME(date_or_timestamp_expr)

Extracts the three-letter day-of-week name from the specified date or timestamp.

  • date_or_timestamp_expr: The date or time expression to extract from

HOUR(time_or_timestamp_expr)

Extracts the corresponding time part from a time or timestamp value.

  • time_or_timestamp_expr:

MINUTE(time_or_timestamp_expr)

Extracts the corresponding time part from a time or timestamp value.

  • time_or_timestamp_expr:

SECOND(time_or_timestamp_expr)

Extracts the corresponding time part from a time or timestamp value.

  • time_or_timestamp_expr:

YEAR(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

YEAROFWEEK(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

YEAROFWEEKISO(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

DAY(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

DAYOFMONTH(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

DAYOFWEEK(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

DAYOFWEEKISO(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

DAYOFYEAR(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

WEEK(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

WEEOKOFYEAR(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

WEEKISO(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

MONTH(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

QUARTER(date_or_timestamp_expr)

Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part

  • date_or_timestamp_expr:

LAST_DAY(date_or_time_expr [, date_part])

Returns the last day of the specified date part for a date or timestamp. Commonly used to return the last day of the month for a date or timestamp.

  • date_or_time_expr:

  • date_part:

MONTHNAME(date_or_timestamp_expr)

Extracts the three-letter month name from the specified date or timestamp.

  • date_or_timestamp_expr:

NEXT_DAY(date_or_time_expr, dow_string)

Returns the date of the first specified DOW (day of week) that occurs after the input date.

  • date_or_time_expr: Specifies the input date; can be a date or timestamp.

  • dow_string: Specifies the day of week used to calculate the date for the previous day. The value can be a string literal or an expression that returns a string. The string must start with the first two characters (case-insensitive) of the day name

PREVIOUS_DAY(date_or_time_expr, dow)

Returns the date of the first specified DOW (day of week) that occurs before the input date.

  • date_or_time_expr: Specifies the input date; can be a date or timestam

  • dow: Specifies the day of week used to calculate the date for the previous day. The value can be a string literal or an expression that returns a string. The string must start with the first two characters (case-insensitive) of the day name

ADD_MONTHS(date_or_timestamp_expr, num_months_expr)

Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information.

  • date_or_timestamp_expr: This is the date or timestamp expression to which you want to add a specified number of months.

  • num_months_expr: This is the number of months you want to add. This should be an integer. It may be positive or negative. If the value is a non-integer numeric value (for example, FLOAT) the value will be rounded to the nearest integer.

EXTRACT(date_or_time_part FROM date_or_time_expr)

Extracts the specified date or time part from a date, time, or timestamp.

  • date_or_time_part:

  • date_or_time_expr:

DATEADD(date_or_time_part, value, date_or_time_expr)

Adds the specified value for the specified date or time part to a date, time, or timestamp.

  • date_or_time_part: This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY.

  • value: This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.

  • date_or_time_expr: must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE

TIMEADD(date_or_time_part, value, date_or_time_expr)

Adds the specified value for the specified date or time part to a date, time, or timestamp.

  • date_or_time_part: This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY

  • value: This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.

  • date_or_time_expr: must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE.

TIMESTAMPADD(date_or_time_part, time_value, date_or_time_expr)

Adds the specified value for the specified date or time part to a date, time, or timestamp.

  • date_or_time_part:

  • time_value:

  • date_or_time_expr:

DATEDIFF(date_or_time_part, date_or_time_expr1, date_or_time_expr2)

Calculates the difference between two date, time, or timestamp expressions based on the date or time part requested. The function returns the result of subtracting the second argument from the third argument.

  • date_or_time_part:

  • date_or_time_expr1: must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.

  • date_or_time_expr2: must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.

TIMEDIFF(date_or_time_part, date_or_time_expr1, time_expr2)

Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.

  • date_or_time_part:

  • date_or_time_expr1: must be a date, a time, a timestamp, or an expression that can be evaluated to one of those. The first value is subtracted from the second value

  • time_expr2: must be a date, a time, a timestamp, or an expression that can be evaluated to one of those.

TIMESTAMPDIFF(date_or_time_part, date_or_time_expr1, date_or_time_expr2)

Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.

  • date_or_time_part:

  • date_or_time_expr1:

  • date_or_time_expr2:

DATE_TRUNC(date_or_time_part, date_or_time_expr)

Truncates a date, time, or timestamp to the specified part. Note that truncation is not the same as extraction. For example: Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp. Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp.

  • date_or_time_part:

  • date_or_time_expr:

CONVERT_TIMEZONE(source_tz, target_tz, source_timestamp_ntz)

Converts a timestamp to another time zone

  • source_tz: String specifying the time zone for the input timestamp. Required for timestamps with no time zone (i.e. TIMEZONE_NTZ)

  • target_tz: String specifying the time zone to which the input timestamp should be converted.

  • source_timestamp_ntz: For the 3-argument version, string specifying the timestamp to convert (must be NTZ).

CONVERT_TIMEZONE(target_tz, source_timestamp)

Converts a timestamp to another time zone

  • target_tz: String specifying the time zone to which the input timestamp should be converted.

  • source_timestamp: For the 2-argument version, string specifying the timestamp to convert (can be any timestamp variant, including NTZ).

HASH(expr [, ...])

Returns a signed 64-bit hash value. Note that HASH never returns NULL, even for NULL inputs.

  • expr:

HASH_AGG([DISTINCT] expr [, ...])

Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. HASH_AGG never returns NULL, even if no input is provided. Empty input "hashes" to 0.

  • expr:

GET_DDL(object_type, namespace_object_name)

Returns a DDL statement that can be used to recreate the specified object. For databases and schemas, GET_DDL is recursive, i.e. it returns the DDL statements for recreating all supported objects within the specified database/schema.

  • object_type: Specifies the type of object for which the DDL is returned.

  • namespace_object_name: Specifies the fully-qualified name of the object for which the DDL is returned.

ABS(num_expr)

Returns the absolute value of a numeric expression.

  • num_expr:

CEIL(input_expr [, scale_expr])

Returns values from input_expr rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point

  • input_expr: The input_expr is the value to be rounded up, and should evaluate to a numeric data type, such as FLOAT or NUMBER.

  • scale_expr: The scale_expr indicates the number of places after the decimal to which to round upward. This should evaluate to an integer.

FLOOR(input_expr [, scale_expr])

Returns values from input_expr rounded to the nearest equal or smaller integer, or to the nearest equal or smaller value with the specified number of places after the decimal point

  • input_expr: The input_expr is the value to be rounded up, and should evaluate to a numeric data type, such as FLOAT or NUMBER.

  • scale_expr: The scale_expr indicates the number of places after the decimal to which to round upward. This should evaluate to an integer.

MOD(expr1, expr2)

Returns the remainder of input expr1 divided by input expr2.

  • expr1: A numeric expression.

  • expr2: A numeric expression.

ROUND(input_expr, scale_expr)

Returns rounded values for input_expr

  • input_expr: The expression to round

  • scale_expr: If the optional scale_expr argument is specified, rounding is performed to the specified number of digits (negative digits round to factors-of-10)

SIGN(expr)

Returns the sign of its argument

  • expr: an expression

TRUNCATE(input_expr, scale_expr)

Rounds the input expression down to the nearest (or equal) integer towards zero.

  • input_expr: the expression to truncate

  • scale_expr: If the optional scale_expr argument is provided, rounding is performed to the specified number of digits (negative digits round to factors-of-10)

CBRT(expr)

Returns the cubic root of a numeric expression.

  • expr: a numeric expression

EXP(real_expr)

Computes Euler's number e raised to a floating-point value.

  • real_expr: a real expression

FACTORIAL(integer_expr)

Computes the factorial of its input. The input argument must be an integer expression in the range of 0 to 33.

  • integer_expr: an integer expression

POWER(x, y)

Returns a number (x) raised to the specified power (y).

  • x:

  • y:

SQRT(expr)

Returns the square-root of a non-negative numeric expression.

  • expr: a non-negative numeric expression.

SQUARE(expr)

Returns the square of a numeric expression, i.e. a numeric expression multiplied by itself.

  • expr: a numeric expression

LN(expr)

Returns the natural logarithm of a numeric expression.

  • expr: a numeric expression

LOG(base, expr)

Returns the logarithm of a numeric expression.

  • base: The "base" to use (e.g. 10 for base 10 arithmetic). This can be of any numeric data type (INTEGER, fixed-point, or floating point).

  • expr: The value for which you want to know the log.

ACOS(real_expr)

Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval [-pi, pi].

  • real_expr: This expression should evaluate to a real number greater than or equal to -1.0 and less than or equal to +1.0.

ACOSH(real_expr)

Computes the inverse (arc) hyperbolic cosine of its input.

  • real_expr: This expression should evaluate to a FLOAT number greater than or equal to 1.0.

ASIN(real_expr)

Computes the inverse sine (arc sine) of its argument; the result is a number in the interval [-pi, pi].

  • real_expr: This expression should evaluate to a real number greater than or equal to -1.0 and less than or equal to +1.0.

ASINH(real_expr)

Computes the inverse (arc) hyperbolic sine of its argument.

  • real_expr: This expression should evaluate to a real number.

ATAN(real_expr)

Computes the inverse tangent (arc tangent) of its argument; the result is a number in the interval [-pi, pi].

  • real_expr: This expression should evaluate to a real number.

ATANH(real_expr)

Computes the inverse (arc) hyperbolic tangent of its argument.

  • real_expr:

COS(real_expr)

Computes the cosine of its argument; the argument should be expressed in radians.

  • real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.

COSH(real_expr)

Computes the hyperbolic cosine of its argument.

  • real_expr: This expression should evaluate to a real number.

COT(real_expr)

Computes the cotangent of its argument; the argument should be expressed in radians.

  • real_expr: This expression should evaluate to a real number.

DEGREES(real_expr)

Converts radians to degrees.

  • real_expr: An expression representing the number of radians.

RADIANS(real_expr)

Converts degrees to radians.

  • real_expr:

SIN(real_expr)

Computes the sine of its argument; the argument should be expressed in radians.

  • real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.

SINH(real_expr)

Computes the hyperbolic sine of its argument.

  • real_expr: This expression should evaluate to a real number.

TAN(real_expr)

Computes the tangent of its argument; the argument should be expressed in radians.

  • real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.

TANH(real_expr)

Computes the hyperbolic tangent of its argument.

  • real_expr: This expression should evaluate to a real number.

ATAN2(real_expr, real_expr)

Computes the inverse tangent (arc tangent) of the ratio of its two arguments (i.e. ATAN2(x,y) = ATAN(x/y)). The result is a number in the interval [-pi, pi].

  • real_expr:

  • real_expr:

PI()

Returns the value of pi as a floating-point value.

HAVERSINE(lat1, lon1, lat2, lon2)

Calculates the great circle distance in kilometers between two points on the Earth's surface, using the Haversine formula. The two points are specified by their latitude and longitude in degrees.

  • lat1: Latitude of Point 1

  • lon1: Longitude of Point 1

  • lat2: Latitude of Point 2

  • lon2: Longitude of Point 2

CHECK_JSON(string_or_variant_expr)

Checks the validity of a JSON document. If the input string is a valid JSON document or a NULL, the output is NULL (i.e. no error). If the input cannot be translated to a valid JSON value, the output string contains the error message.

  • string_or_variant_expr: A VARIANT or string value (or expression) to check. If the expression is of type VARIANT, it should contain a string.

CHECK_XML(string_or_variant_expr)

Checks the validity of an XML document. If the input string is NULL or a valid XML document, the output is NULL. In case of an XML parsing error, the output string contains the error message.

  • string_or_variant_expr: A VARIANT or string value (or expression) to check. If the expression is of type VARIANT, it should contain a string.

PARSE_JSON(expr)

Interprets an input string as a JSON document, producing a VARIANT value.

  • expr: An expression of string type (e.g. VARCHAR) that holds valid JSON information.

PARSE_XML(expr)

Interprets an input string as an XML document, producing an OBJECT value. If the input is NULL, the output is NULL.

  • expr: An expression of string type (e.g. VARCHAR) that holds valid JSON information.

STRIP_NULL_VALUE(expr)

Converts a JSON "null" value to a SQL NULL value. All other variant values are passed unchanged.

  • expr: an expression to strip data from

ARRAY_APPEND(array, new_element)

Returns an array containing all elements from the source array as well as the new element. The new element is located at end of the array.

  • array: The source array.

  • new_element: The element to be appended. The element may be of almost any data type. The data type does not need to match the data type(s) of the existing elements in the array.

ARRAY_PREPEND(array, new_element)

Returns an array containing the new element as well as all elements from the source array. The new element is positioned at the beginning of the array.

  • array: The source array.

  • new_element: The element to be prepended.

ARRAY_CAT(array1, array2)

Returns a concatenation of two arrays.

  • array1: The source array.

  • array2: The array to be appended to array1.

ARRAY_INSERT(array, pos, new_element)

Returns an array containing all elements from the source array as well as the new element.

  • array: The source array.

  • pos: A (zero-based) position in the source array. The new element is inserted at this position. The original element from this position (if any) and all subsequent elements (if any) are shifted by one position to the right in the resulting array (i.e. inserting at position 0 has the same effect as using ARRAY_PREPEND). A negative position is interpreted as an index from the back of the array (e.g. -1 results in insertion before the last element in the array).]

  • new_element: The element to be inserted. The new element is located at position pos. The relative order of the other elements from the source array is preserved.

ARRAY_COMPACT(array1)

Returns a compacted array with missing and null values removed, effectively converting sparse arrays into dense arrays.

  • array1: The source array.

ARRAY_CONSTRUCT([ expr1 ] [ , expr2 [ , ... ] ])

Returns an array constructed from zero, one, or more inputs.

  • expr1:

  • expr2:

ARRAY_CONSTRUCT_COMPACT([ expr1 ] [ , expr2 [ , ... ] ])

Returns an array constructed from zero, one, or more inputs.

  • expr1:

  • expr2:

ARRAY_CONTAINS(variant, array)

Takes a VARIANT and an ARRAY value as inputs and returns True if the VARIANT is contained in the ARRAY.

  • variant:

  • array:

ARRAY_POSITION(variant_expr, array)

Returns the index of the first occurrence of an element in an array.

  • variant_expr: This expression should evaluate to a VARIANT value. The function searches for the first occurrence of this value in the array.

  • array: The array to be searched.

ARRAY_SIZE(array_or_variant)

Returns the size of the input array. A variation of ARRAY_SIZE takes a VARIANT value as input. If the VARIANT value contains an array, the size of the array is returned; otherwise, NULL is returned if the value is not an array.

  • array_or_variant:

ARRAY_SLICE(array, from, to)

Returns an array constructed from a specified subset of elements of the input array.

  • array: The source array of which a subset of the elements are used to construct the resulting array.

  • from: A position in the source array. The position of the first element is 0. Elements from positions less than from are not included in the resulting array.

  • to: A position in the source array. Elements from positions equal to or greater than to are not included in the resulting array.

ARRAY_TO_STRING(array, separator_string)

Returns an input array converted to a string by casting all values to strings (using TO_VARCHAR) and concatenating them (using the string from the second argument to separate the elements).

  • array: The array of elements to convert to a string.

  • separator_string: The string to put between each element, typically a space, comma, or other human-readable separator.

ARRAYS_OVERLAP(array1, array2)

Compares whether two arrays have at least one element in common. Returns TRUE if there is at least one element in common; otherwise returns FALSE. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.

  • array1: an array

  • array2: an array

OBJECT_AGG(key, value)

Returns one OBJECT per group. For each (key, value) input pair, where key must be a VARCHAR and value must be a VARIANT, the resulting OBJECT contains a key:value field.

  • key:

  • value:

OBJECT_CONSTRUCT([key1, value1 [, keyN, valueN, ...]])

Returns an object constructed from the arguments.

  • key1:

  • value1:

  • keyN:

  • valueN:

OBJECT_DELETE(object, key1 [, key2, ...])

Returns an object containing the contents of the input (i.e.source) object with one or more keys removed.

  • object: The source object.

  • key1: Key to be omitted from the returned object.

  • key2: Key to be omitted from the returned object.

OBJECT_INSERT(object, key, value [, updateFlag])

Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).

  • object: The source object into which the new key-value pair is inserted.

  • key: The new key to be inserted into the object. Must be different from all existing keys in the object, unless updateFlag is set to TRUE.

  • value: The value associated with the key.

  • updateFlag: Boolean flag that, when set to TRUE, specifies the input value is used to update/overwrite an existing key in the object, rather than inserting a new key-value pair.

XMLGET(type, tag_name [, instance_num])

Extracts an XML element object (often referred to as simply a "tag") from a content of outer XML element object by the name of the tag and its instance number (counting from 0)

  • type:

  • tag_name:

  • instance_num: can be omitted, in which case the default value 0 is used.

GET(expr1, expr2)

Extracts a value from an object or array; returns NULL if either of the arguments is NULL.

  • expr1: An Object, Variant, or Array

  • expr2: A string value or an integer, which can be a constant or an expression

AS_ARRAY(variant_expr)

Casts a VARIANT value to an array.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_BINARY(variant_expr)

Casts a VARIANT value to a binary string.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_CHAR(variant_expr)

Casts a VARIANT value to a string. Does not convert values of other types into string.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_VARCHAR(variant_expr)

Casts a VARIANT value to a string. Does not convert values of other types into string.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_DATE(variant_expr)

Casts a VARIANT value to a date. Does not convert from timestamps.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_DOUBLE(variant_expr)

Casts a VARIANT value to a floating-point value.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_REAL(variant_expr)

Casts a VARIANT value to a floating-point value.

  • variant_expr: An expression that evaluates to a value of type VARIANT.

AS_INTEGER(variant_expr)

Casts a VARIANT value to an integer. Does not match non-integer values.