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
Return all columns:
SELECT * FROM [DemoDB].[PUBLIC].ProductsRename a column:
SELECT [ProductName] AS MY_ProductName FROM [DemoDB].[PUBLIC].ProductsCast a column's data as a different data type:
SELECT CAST(Price AS VARCHAR) AS Str_Price FROM [DemoDB].[PUBLIC].ProductsSearch data:
SELECT * FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM [DemoDB].[PUBLIC].ProductsReturn the number of unique items matching the query criteria:
SELECT COUNT(DISTINCT ProductName) FROM [DemoDB].[PUBLIC].ProductsReturn the unique items matching the query criteria:
SELECT DISTINCT ProductName FROM [DemoDB].[PUBLIC].ProductsSummarize data:
SELECT ProductName, MAX(Price) FROM [DemoDB].[PUBLIC].Products GROUP BY ProductNameSee Aggregate Functions below for details.
Retrieve data from multiple tables.
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerIdSee JOIN Queries below for details.
Sort a result set in ascending order:
SELECT Id, ProductName FROM [DemoDB].[PUBLIC].Products ORDER BY ProductName ASCRestrict a result set to the specified number of rows:
SELECT Id, ProductName FROM [DemoDB].[PUBLIC].Products LIMIT 10Parameterize 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.
|
COUNT(DISTINCT)
Returns the number of distinct, non-null field values matching the query criteria.
|
AVG
Returns the average of the column values.
|
MIN
Returns the minimum column value.
|
MAX
Returns the maximum column value.
|
SUM
Returns the total sum of the column values.
|
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:
|
Left Join
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
|
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.
|
L_YESTERDAY()
The previous day.
|
L_TOMORROW()
The following day.
|
L_LAST_WEEK()
Every day in the preceding week.
|
L_THIS_WEEK()
Every day in the current week.
|
L_NEXT_WEEK()
Every day in the following 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.
|
L_NEXT_N_DAYS(n)
The following n days, including the current day.
|
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.
|
L_NEXT_N_WEEKS(n)
Every day in every week, starting the following week, and ending n weeks in the future.
|
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.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_OBJECT(variant_expr)
Casts a VARIANT value to an object.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIME(variant_expr)
Casts a VARIANT value to a time value. Does not convert from timestamps.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIMESTAMP_LTZ(variant_expr)
Casts a VARIANT value to the respective TIMESTAMP value with local time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIMESTAMP_NTZ(variant_expr)
Casts a VARIANT value to the respective TIMESTAMP value with no time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIMESTAMP_TZ(variant_expr)
Casts a VARIANT value to the respective TIMESTAMP value with time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_DECIMAL(variant_expr [, precision [, scale]])
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
AS_NUMBER(variant_expr [, precision [, scale]])
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
STRTOK_TO_ARRAY(string [, delimiter])
Tokenizes the given string using the given set of delimiters and returns the tokens as an array. If either parameter is a NULL, a NULL is returned. An empty array is returned in case tokenization produces no tokens.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
STRTOK_TO_SPLIT_TO_TABLE(string [, delimiter])
Tokenizes a string with the given set of delimiters and flattens the results into rows.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
TO_ARRAY(expr)
Converts the input expression into an array: If the input is an ARRAY, or VARIANT containing an array value, the result is unchanged. For NULL or a JSON null input, returns NULL. For any other value, the result is a single-element array containing this value.
expr: An expression of any data type.
TO_JSON(expr)
Converts any VARIANT value to a string containing the JSON representation of the value. If the input is NULL, the result is also NULL.
expr: An expression of type VARIANT that holds valid JSON information.
TO_OBJECT(expr)
Converts the input value to an object
expr: An expression of any data type.
TO_VARIANT(expr)
Converts any value to VARIANT value or NULL (if input is NULL).
expr: An expression of any data type.
TO_XML(expr1)
Converts any VARIANT value to a string containing the XML representation of the value. If the input is NULL, the result is also NULL.
expr1: The Variant to convert
IS_ARRAY(variant_expr)
Returns TRUE if its VARIANT argument contains an ARRAY value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_BOOLEAN(variant_expr)
Returns TRUE if its VARIANT argument contains an Boolean value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_BINARY(variant_expr)
Returns TRUE if its VARIANT argument contains an binary value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_CHAR(variant_expr)
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_VARCHAR(variant_expr)
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DATE(variant_expr)
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DATE_VALUE(variant_expr)
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DECIMAL(variant_expr)
Returns TRUE if its VARIANT argument contains an fixed-point decimal value or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DOUBLE(variant_expr)
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_REAL(variant_expr)
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_INTEGER(variant_expr)
Returns TRUE if its VARIANT argument contains an integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_NULL_VALUE(variant_expr)
Returns TRUE if its VARIANT argument contains an NULL value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_OBJECT(variant_expr)
Returns TRUE if its VARIANT argument contains an OBJECT value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIME(variant_expr)
Returns TRUE if its VARIANT argument contains an TIME value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIMESTAMP_LTZ(variant_expr)
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIMESTAMP_NTZ(variant_expr)
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIMESTAMP_TZ(variant_expr)
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
TYPEOF(column)
Reports the type of a value stored in a VARIANT column. The type is returned as a string.
column: The column to detect the type of
REGEXP_COUNT(subject, pattern [, position, parameters])
Returns the number of times that a pattern occurs in a string.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
parameters: String of one or more characters that specifies the parameters used for searching for matches
REGEXP_INSTR(subject, pattern [, position, occurrence, option, parameters])
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
option: Specifies whether to return the offset of the first character of the match (0) or the offset of the first character following the end of the match (1). Default: 0
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
REGEXP_LIKE(subject, pattern [, parameters ])
Returns true if the subject matches the pattern. Both expressions must be text expressions.
subject: Subject to match.
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
REGEXP_REPLACE(subject, pattern [, replacement [, position [, occurrence [, parameters]]]])
Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. If no matches are found, returns the original subject.
subject: Subject to match.
pattern: Pattern to match.
replacement: String the replaces the substrings matched by the pattern. If an empty string is specified, the function removes all matched patterns and returns the resulting string. Default: '' (empty string).
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
occurrence: Specifies which occurrence of the pattern to replace. If 0 is specified, all occurrences are replaced. Default: 0 (all occurrences)
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
REGEXP_SUBSTR(subject, pattern [, position, occurrence, parameters])
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
RLIKE(subject, pattern, parameters)
Returns true if the subject matches the specified pattern. Both inputs must be text expressions.
subject: Subject to match
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
ASCII(input)
Returns the ASCII code for the first character of a string. If the string is empty, a value of 0 is returned.
input: The string for which the ASCII code for the first character in the string is returned.
BIT_LENGTH(string_or_binary)
Returns the length of a string or binary value in bits. Snowflake doesn't use fractional bytes so length is always calculated as 8 * OCTET_LENGTH.
string_or_binary: The string or binary value for which the length is returned.
CHARINDEX(expr1, expr2 [, start_pos ])
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr1). Default: 1
CHAR(input)
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
CHR(input)
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
CONCAT(expr1, expr2)
Concatenates two strings or two binary values. If one of the them is null, the result is also null.
expr1: A string to concatenate
expr2: A string to concatenate
CONTAINS(expr1, expr2)
Returns true if expr1 contains expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
EDITDISTANCE(expr1, expr2)
Computes the Levenshtein distance between two input strings. It is the number of single-character insertions, deletions or substitutions needed to convert one string to another.
expr1: A string
expr2: A string
ENDSWITH(expr1, expr2)
Returns TRUE if the first expression ends with second expression. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
ILIKE(subject, pattern [, escape ])
Allows matching of strings based on comparison with a pattern. Unlike the LIKE function, string matching is case-insensitive. LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
INITCAP(expr [, delimiters ])
Returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.
expr: The input string
delimiters: an optional argument specifying a string of one or more characters that INITCAP uses as separators for words in the input expression:
INSERT(base_expr, pos, len, insert_expr)
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value. This function should not be confused with the INSERT DML command.
base_expr: The string or BINARY expression for which you want to insert/replace characters.
pos: The offset at which to start inserting characters. This is 1-based, not 0-based.
len: The number of characters (starting at pos) that you want to replace. Valid values range from 0 to the number of characters between pos and the end of the string.
insert_expr: The string to insert into the base_expr. If this string is empty, and if len is greater than zero, then effectively the operation becomes a delete (some characters are deleted, and none are added).
LEFT(string_expr, length_expr)
Returns a leftmost substring of its input. LEFT(STR,N) is equivalent to SUBSTR(STR,1,N).
string_expr: The string expression
length_expr: The length to extract
LENGTH(expr)
Returns the length of a input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.
expr: The expression to measure
LIKE(subject, pattern [, escape ])
Allows case-sensitive matching of strings based on comparison with a pattern. For case-insensitive matching, use ILIKE instead.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
LOWER(expr)
Returns the input string (expr) with all characters converted to lowercase.
expr: The input string
LPAD(base, n [, pad])
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
base: The base string to pad
n: The number of characters to bad
pad: The character to pad
LTRIM(expr [, characters])
Removes leading characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the left side of expr. The default value is ' ' (a single blank space character),
OCTET_LENGTH(string_or_binary)
Returns the length of a string or binary value in bytes. This will be the same as LENGTH for ASCII strings and greater than LENGTH for strings using Unicode code points. For binary, this is always the same as LENGTH.
string_or_binary: The string or binary value for which the length is returned.
PARSE_IP(expr, type [, permissive])
Returns a JSON object consisting of all the components from a valid INET (Internet Protocol) or CIDR (Classless Internet Domain Routing) IPv4 or IPv6 string.
expr: A string expression.
type: Identifies the type of IP address. Supports either INET or CIDR; case-insenstitive.
permissive: Flag that determines how parse errors are handled:
PARSE_URL(string [, permissive])
Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI.
string: String to parse.
permissive: Flag that determines how parse errors are handled
POSITION(expr1, expr2 [, start_pos ])
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr2).
POSITION(expr1 IN expr2)
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
REPEAT(input, n)
Builds a string by repeating the input for the specified number of times.
input: The input string from which the output string is built.
n: The number of times the input string should be repeated. The minimum valid number is 0 (which results in an empty string).
REPLACE(subject, pattern [, replacement])
Removes all occurrences of a specified substring, and optionally replaces them with another string
subject: The subject is the string in which to do the replacements. Typically, this is a column, but it can be a literal.
pattern: This is the substring that you want to replace. Typically, this is a literal, but it can be a column or expression. Note that this is not a "regular expression"; if you want to use regular expressions to search for a pattern, use the REGEXP_REPLACE function.
replacement: This is the value used as a replacement for the pattern. If this is omitted, or is an empty string, then the REPLACE function simply deletes all occurrences of the pattern.
REVERSE(subject)
everses the order of characters in a string, or of bytes in a binary value.
subject: The string to reverse
RIGHT(string_expr, length_expr)
Returns a rightmost substring of its input.
string_expr: The string expression
length_expr: The length to extract
RPAD(base, n [, pad])
Right-pads a string or binary value with characters from another string.
base: The base string to pad
n: The number of times to pad
pad: The string to pad with
RTRIM(expr [, characters])
Removes trailing characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the right side of expr:
RTRIMMED_LENGTH(string_expr)
Returns the length of its argument, minus trailing whitespace, but including leading whitespace.
string_expr: The string expression to measure
SPACE(n)
Builds a string consisting of the specified number of blank spaces.
n: The number of blank spaces used to build the string.
SPLIT(string, separator)
Splits a given string with a given separator and returns the result in an array of strings. Contiguous split strings in the source string, or the presence of a split string at the beginning or end of the source string, results in an empty string in the output. An empty separator string results in an array containing only the source string. If either parameter is a NULL, a NULL is returned.
string: Text to be split into parts.
separator: Text to split string by.
SPLIT_TO_TABLE(string, delimiter)
Splits a string with the given delimiter and flattens the results into rows.
string: Text to be split into parts.
delimiter: Text to split string by.
STRTOK(string [, delimiter] [, partNr])
Tokenizes a given string and returns the requested part.
string: Text to be tokenized.
delimiter: Text representing the set of delimiters to tokenize on. Each character in the delimiter string is a delimiter. If the delimiter is empty, and the string is empty, then the function returns NULL. If the delimiter is empty, and the string is non empty, then the whole string will be treated as one token. The default value of the delimiter is a single space character.
partNr: Requested token (1-based, i.e. the first token is token number 1, not token number 0). If the token number is out of range, then NULL is returned. The default value is 1.
SPLIT_PART(string, delimiter, partNr)
Splits a given string and returns the requested part. If a part does not exist, an empty string is returned. If any parameter is NULL, NULL is returned.
string: Text to be split into parts.
delimiter: Text representing the delimiter to split by.
partNr: Requested part of the split (1-based). 0 is treated as 1. If the value is negative, the parts are counted from the right side of the string.
STARTSWITH(expr1, expr2)
Returns true if expr1 starts with expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
SUBSTRING(base_expr, start_expr [, length_expr])
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
COMPRESS(input, method)
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY or string value (or expression) to be compressed.
method: A string with compression method and optional compression level. Supported methods are: SNAPPY. ZLIB. ZSTD. BZ2. The compression level is specified in parentheses, for example: zlib(1). The compression level is a non-negative integer. 0 means default level (same as omitting the compression level). The compression level is ignored if the method doesn't support compression levels.
DECOMPRESS_BINARY(input, method)
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS. If you attempt to decompress a compressed string, rather than a compressed BINARY value, you will not get an error; the function will return a BINARY value. See the Usage Notes below for details.
method: The compression method originally used to compress the input.
DECOMPRESS_STRING(input, method)
Decompresses the compressed BINARY input parameter to a string.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS.
method: The compression method originally used to compress the input.
SUBSTR(base_expr, start_expr [, length_expr])
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
TRANSLATE(subject, sourceAlphabet, targetAlphabet)
Translates subject from the characters in sourceAlphabet to the characters in targetAlphabet.
subject: A string expression that is translated. If a character in subject is not contained in sourceAlphabet, the character is added to the result without any translation.
sourceAlphabet: A string with all characters that are modified by this function. Each character is either translated to the corresponding character in the targetAlphabet or omitted in the result if the targetAlphabet has no corresponding character (i.e. has less characters than the sourceAlphabet).
targetAlphabet: A string with all characters that are used to replace characters from the sourceAlphabet.
TRIM(expr [, characters])
Removes leading and trailing characters from a string.
expr: A string expression to be trimmed.
characters: One or more characters to remove from the left and right side of expr: The default value is ' ' (a single blank space character), i.e. if no characters are specified, all leading and trailing blank spaces are removed.
UNICODE(input)
Returns the Unicode code point for the first Unicode character in a string. If the string is empty, a value of 0 is returned.
input: The string for which the Unicode code point for the first character in the string is returned.
UPPER(expr)
Returns the input string expr with all characters converted to uppercase.
expr: The input string
BASE64_DECODE_BINARY(input [, alphabet])
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
BASE64_DECODE_STRING(input [, alphabet])
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
BASE64_ENCODE(input [, max_line_length ] [, alphabet ] )
Encodes the input (string or binary) using Base64 encoding.
input: A string or binary expression to be encoded.
max_line_length: A positive integer that specifies the maximum number of characters in a single line of the output.
alphabet: A string consisting of up to three ASCII characters
HEX_DECODE_BINARY(input)
Decodes a hex-encoded string to a binary.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
HEX_DECODE_STRING(input)
Decodes a hex-encoded string to a string.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
HEX_ENCODE(input [, case])
Encodes the input using hexadecimal (also 'hex' or 'base16') encoding. The result is comprised of 16 different symbols: The numbers '0' to '9' as well as the letters 'A' to 'F'
input: A binary or string expression to be encoded.
case: This optional boolean argument controls the case of the letters ('A', 'B', 'C', 'D', 'E' and 'F') used in the encoding. The default value is 1 and indicates that uppercase letters are used. The value 0 indicates that lowercase letters are used. All other values are illegal and result in an error.
TRY_BASE64_DECODE_BINARY(input [, alphabet])
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
TRY_BASE64_DECODE_STRING(input [, alphabet])
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
TRY_HEX_DECODE_BINARY(input)
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
TRY_HEX_DECODE_STRING(input)
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
MD5(msg)
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
MD5_HEX(msg)
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed
MD5_BINARY(msg)
Returns a 16-byte BINARY value containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
MD5_NUMBER(msg)
Returns the 128-bit MD5 message digest interpreted as a signed 128-bit big endian number. This representation is useful for maximally efficient storage and comparison of MD5 digests.
msg: A string expression, the message to be hashed.
SHA1(msg)
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
SHA1_HEX(msg)
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
SHA1_BINARY(msg)
Returns a 20-byte binary containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
SHA2(msg [, digest_size])
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
SHA2_HEX(msg [, digest_size])
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
SHA2_BINARY(msg [, digest_size])
Returns a binary containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
ANY_VALUE([DISTINCT] expr)
Returns some value of the expression from the group. The result is non-deterministic.
expr: A group of values to choose from
AVG([DISTINCT] expr)
Returns the average of non-NULL records. If all records inside a group are NULL, NULL is returned.
expr: Expression is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
CORR(y, c)
Returns the correlation coefficient for non-null pairs in a group. It is computed for non-null pairs using the following formula: COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y))
y:
c:
COUNT([DISTINCT] expr1 [, expr2])
Returns either the number of non-NULL records for the specified columns, or a total number of records.
expr1: A column name.
expr2: You may include additional column name(s) if you wish. For example, you could list the number of distinct combinations of last name and first name.
COVAR_POP(x, y)
Returns the population covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*) Where x is the independent variable and y is the dependent variable.
x:
y:
COVAR_SAMP(y, c)
Returns the sample covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / (COUNT(*) - 1) Where x is the independent variable and y is the dependent variable.
y:
x:
LISTAGG([DISTINCT] expr, delimiter)
Returns the concatenated input values, separated by the delimiter string.
expr: The expression (typically a column name) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter: A string, or an expression that evaluates to a string. In practice, this is usually a single-character string. The string should be surrounded by single quotes, as shown in the examples below.
MAX(expr)
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
MIN(expr)
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
MEDIAN(expr)
Determines the median of a set of values.
expr: The expression must evaluate to a numeric data type (INTEGER, FLOAT, DECIMAL, or equivalent).
PERCENTILE_CONT(percentile)
Return a percentile value based on a continuous distribution of the input column (specified in order_by_expr). If no input row lies exactly at the desired percentile, the result is calculated using linear interpolation of the two nearest input values. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
PERCENTILE_DISC(percentile)
Returns a percentile value based on a discrete distribution of the input column (specified in order_by_expr). The returned value is that whose row has the smallest CUME_DIST value that is greater than or equal to the given percentile. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
STDDEV(expr)
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value.
STDDEV_SAMP([DISTINCT] expr)
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value (integer, floating point, or fixed point).
STDDEV_POP([DISTINCT] x)
Returns the population standard deviation (square root of variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
x:
SUM([DISTINCT] expr)
Returns the sum of non-NULL records for expr. If the DISTINCT keyword is used, the sum of unique non-null values is computed. If all records inside a group are NULL, a value of NULL is returned.
expr: The expression to sum
VAR_POP([DISTINCT] x)
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VARIANCE_POP([DISTINCT] x)
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VAR_SAMP([DISTINCT] x)
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VARIANCE([DISTINCT] x)
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VARIANCE_SAMP([DISTINCT] x)
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
CUME_DIST()
Finds the cumulative distribution of a value with regard to other values within the same window partition.
DENSE_RANK()
Returns the rank of a value within a group of values, without gaps in the ranks. The rank value starts at 1 and continues up sequentially. If two values are the same, they will have the same rank.
FIRST_VALUE(expr)
Returns the first value within an ordered group of values.
expr:
LAG(expr [, offset, default])
Accesses data in a previous row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows backward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr
LAST_VALUE(expr)
Returns the last value within an ordered group of values.
expr:
LEAD(expr, offset, default)
Accesses data in a subsequent row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows forward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr.
NTH_VALUE(expr, n)
Returns the nth value (up to 1000) within an ordered group of values.
expr:
n: Input value n cannot be greater than 1000.
NTILE(constant_value)
Divides an ordered data set equally into the number of buckets specified by constant_value. Buckets are sequentially numbered 1 through constant_value.
constant_value: The desired number of buckets; must be a positive integer value.
PERCENT_RANK()
Returns the relative rank of a value within a group of values.
RANK()
Returns the rank of a value within an ordered group of values.
ROW_NUMBER()
Returns a unique row number for each row within a window partition.
WIDTH_BUCKET(expr, min_value, max_value, num_buckets)
Constructs equi-width histograms, in which the histogram range is divided into intervals of identical size, and returns the bucket number into which the value of an expression falls, after it has been evaluated. The function returns an integer value or null (if any input is null).
expr: The expression for which the histogram is created. This expression must evaluate to a numeric value or to a value that can be implicitly converted to a numeric value.
min_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
max_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
num_buckets: The desired number of buckets; must be a positive integer value. A value from the expression is assigned to each bucket, and the function then returns the corresponding bucket number.
BITAND_AGG(expr)
Returns the bitwise AND value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
BITOR_AGG(expr)
Returns the bitwise OR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
BITXOR_AGG([DISTINCT] expr)
Returns the bitwise XOR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
ARRAY_AGG([DISTINCT] expr)
Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
expr: The expression (typically a column name) that determines the values to be put into the list
REGR_AVGX(y, x)
Returns the average of the independent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
REGR_COUNT(y, x)
Returns the number of non-null number pairs in a group.
y:
x:
REGR_INTERCEPT(y, x)
Returns the intercept of the univariate linear regression line for non-null pairs in a group. It is computed for non-null pairs using the following formula: AVG(y)-REGR_SLOPE(y,x)*AVG(x) Where x is the independent variable and y is the dependent variable
y:
x:
REGR_R2(y, x)
Returns the coefficient of determination for non-null pairs in a group.
y:
x:
REGR_SLOPE(y, x)
Returns the slope of the linear regression line for non-null pairs in a group
y:
x:
REGR_SXX(y, x)
Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs.
y:
x:
REGR_SXY(y, x)
Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs.
y:
x:
REGR_SYY(y, x)
Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs.
y:
x:
REGR_AVGY(y, x)
Returns the average of the dependent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
APPROX_COUNT_DISTINCT([DISTINCT] expr [, ...])
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
HLL([DISTINCT] expr [, ...])
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
HLL_COMBINE([DISTINCT] state)
Combines (merges) input states into a single output state. This allows scenarios where HLL_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using HLL_COMBINE, producing the same output state as a single run of HLL_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE.
HLL_ESTIMATE(state)
Returns the cardinality estimate for the given HyperLogLog state. A HyperLogLog state produced by HLL_ACCUMULATE and HLL_COMBINE can be used to compute a cardinality estimate using the HLL_ESTIMATE function.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE or HLL_COMBINE.
HLL_ACCUMULATE([DISTINCT] expr)
Returns the HyperLogLog state at the end of aggregation.
expr:
HLL_EXPORT(binary_expr)
Converts input in BINARY format to OBJECT format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports converting states from the BINARY format to an OBJECT (which can be printed and exported as JSON), and vice versa.
binary_expr:
HLL_IMPORT(obj)
Converts input in OBJECT format to BINARY format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports using HLL_IMPORT to convert states from an OBJECT format to BINARY, and vice versa.
obj:
APPROXIMATE_JACCARD_INDEX([DISTINCT] expr [, ...])
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
APPROXIMATE_SIMILARITY([DISTINCT] expr [, ...])
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
MINHASH(k, [DISTINCT] expr [, ...])
Returns a MinHash state containing an array of size k constructed by applying k number of different hash functions to the input rows and keeping the minimum of each hash function. This MinHash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with one or more other MinHash states.
k: specifies the number of hash functions to be created. The larger the value, the better the approximation; however, this value has a linear impact on the computation time for estimating similarity using APPROXIMATE_SIMILARITY. The suggested value is 100.
expr:
MINHASH_COMBINE([DISTINCT] state)
Combines input MinHash states into a single MinHash output state. This Minhash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with other MinHash states.
state: Input MinHash state must have MinHash arrays of equal length.
APPROX_TOP_K(expr, k, counters)
Uses Space-Saving to return an approximation of the most frequent values in the input, along with their approximate frequencies. The output is a JSON array of arrays. In the inner arrays, the first entry is a value in the input, and the second entry corresponds to its estimated frequency in the input. The outer array contains k items, sorted by descending frequency.
expr: The expression (e.g. column name) for which you want to find the most common values.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
APPROX_TOP_K_ACCUMULATE(expr, counters)
Returns the Space-Saving summary at the end of aggregation. (For more information about the Space-Saving summary, see Estimating Frequent Values.) The function APPROX_TOP_K discards its internal, intermediate state when the final cardinality estimate is returned. However, in certain advanced use cases, such as estimating incremental frequent values during bulk loading, you might want to keep the intermediate state, in which case you would use APPROX_TOP_K_ACCUMULATE instead of APPROX_TOP_K..
expr: The expression (e.g. column name) for which you want to find the most common values.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values
APPROX_TOP_K_COMBINE(state [, counters])
Combines (merges) input states into a single output state. This allows scenarios where APPROX_TOP_K_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using APPROX_TOP_K_COMBINE, producing the same output state as a single run of APPROX_TOP_K_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
APPROX_TOP_K_ESTIMATE(state [, k])
Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. (For more information about the Space-Saving summary, see Estimating Frequent Values.)
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE or APPROX_TOP_K_COMBINE.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
APPROX_PERCENTILE(expr, percentile)
Returns an approximated value for the desired percentile (i.e. if column c has n numbers, then APPROX_PERCENTILE(c, p) returns a number such that approximately n * p of the numbers in c are smaller than the returned number).
expr: A valid expression, such as a column name, that evaluates to a numeric value.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
APPROX_PERCENTILE_ACCUMULATE(expr)
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. (For more information about t-Digest, see: Estimating Percentile Values.)
expr: A valid expression, such as a column name, that evaluates to a numeric value.
APPROX_PERCENTILE_COMBINE(state)
Combines (merges) percentile input states into a single output state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE.
APPROX_PERCENTILE_ESTIMATE(state, percentile)
Returns the desired approximated percentile value for the specified t-Digest state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE or APPROX_PERCENTILE_COMBINE.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
GROUPING(expr1 [, ...])
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
GROUPING_ID(expr1 [, ...])
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
SYSTEMABORT_SESSION(session_id)
Aborts the specified session.
session_id: Identifier for the session to abort.
SYSTEMABORT_TRANSACTION(transaction_id)
Aborts the specified transaction, if it is running. If the transaction has already been committed or rolled back, then the state of the transaction is not altered.
transaction_id: Identifier for the transaction to abort. To obtain transaction IDs, you can use the SHOW TRANSACTIONS or SHOW LOCKS commands.
SYSTEMCANCEL_ALL_QUERIES(session_id)
Cancels all active/running queries in the specified session.
session_id: Identifier for the session for which to cancel all queries.
SYSTEMCANCEL_QUERY(query_id)
Cancels the specified query (or statement) if it is currently active/running.
query_id: Identifier for the query to cancel.
SYSTEMPIPE_FORCE_RESUME(pipe_name)
Forces a pipe paused using ALTER PIPE to resume. This is necessary if the pipe owner transfers ownership of the pipe to another role while the pipe is paused.
pipe_name: Pipe to resume running.
SYSTEMWAIT(amount [, time_unit])
Waits for a specified amount of time before proceeding.
amount: Number specifying the amount of time to wait as determined by time_unit.
time_unit: Time unit for amount. Accepted values are DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS. The unit should be in single quotes
SYSTEMLAST_CHANGE_COMMIT_TIME(object_name)
Returns the commit time of the last DML change performed on a table or a view. In case of a view, the function returns the latest commit time of all the objects referenced in the view.
object_name: ecifies the table or view for which the commit time for the last DML is returned
SYSTEMPIPE_STATUS(pipe_name)
Retrieves a JSON representation of the current status of a pipe.
pipe_name: Pipe for which you want to retrieve the current status.
SYSTEMTYPEOF(expr)
Returns a string representing the SQL data type associated with an expression.
expr:
Predicate 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.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_OBJECT(variant_expr)
Casts a VARIANT value to an object.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIME(variant_expr)
Casts a VARIANT value to a time value. Does not convert from timestamps.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIMESTAMP_LTZ(variant_expr)
Casts a VARIANT value to the respective TIMESTAMP value with local time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIMESTAMP_NTZ(variant_expr)
Casts a VARIANT value to the respective TIMESTAMP value with no time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_TIMESTAMP_TZ(variant_expr)
Casts a VARIANT value to the respective TIMESTAMP value with time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
AS_DECIMAL(variant_expr [, precision [, scale]])
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
AS_NUMBER(variant_expr [, precision [, scale]])
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
STRTOK_TO_ARRAY(string [, delimiter])
Tokenizes the given string using the given set of delimiters and returns the tokens as an array. If either parameter is a NULL, a NULL is returned. An empty array is returned in case tokenization produces no tokens.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
STRTOK_TO_SPLIT_TO_TABLE(string [, delimiter])
Tokenizes a string with the given set of delimiters and flattens the results into rows.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
TO_ARRAY(expr)
Converts the input expression into an array: If the input is an ARRAY, or VARIANT containing an array value, the result is unchanged. For NULL or a JSON null input, returns NULL. For any other value, the result is a single-element array containing this value.
expr: An expression of any data type.
TO_JSON(expr)
Converts any VARIANT value to a string containing the JSON representation of the value. If the input is NULL, the result is also NULL.
expr: An expression of type VARIANT that holds valid JSON information.
TO_OBJECT(expr)
Converts the input value to an object
expr: An expression of any data type.
TO_VARIANT(expr)
Converts any value to VARIANT value or NULL (if input is NULL).
expr: An expression of any data type.
TO_XML(expr1)
Converts any VARIANT value to a string containing the XML representation of the value. If the input is NULL, the result is also NULL.
expr1: The Variant to convert
IS_ARRAY(variant_expr)
Returns TRUE if its VARIANT argument contains an ARRAY value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_BOOLEAN(variant_expr)
Returns TRUE if its VARIANT argument contains an Boolean value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_BINARY(variant_expr)
Returns TRUE if its VARIANT argument contains an binary value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_CHAR(variant_expr)
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_VARCHAR(variant_expr)
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DATE(variant_expr)
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DATE_VALUE(variant_expr)
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DECIMAL(variant_expr)
Returns TRUE if its VARIANT argument contains an fixed-point decimal value or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_DOUBLE(variant_expr)
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_REAL(variant_expr)
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_INTEGER(variant_expr)
Returns TRUE if its VARIANT argument contains an integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_NULL_VALUE(variant_expr)
Returns TRUE if its VARIANT argument contains an NULL value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_OBJECT(variant_expr)
Returns TRUE if its VARIANT argument contains an OBJECT value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIME(variant_expr)
Returns TRUE if its VARIANT argument contains an TIME value.
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIMESTAMP_LTZ(variant_expr)
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIMESTAMP_NTZ(variant_expr)
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
IS_TIMESTAMP_TZ(variant_expr)
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
TYPEOF(column)
Reports the type of a value stored in a VARIANT column. The type is returned as a string.
column: The column to detect the type of
REGEXP_COUNT(subject, pattern [, position, parameters])
Returns the number of times that a pattern occurs in a string.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
parameters: String of one or more characters that specifies the parameters used for searching for matches
REGEXP_INSTR(subject, pattern [, position, occurrence, option, parameters])
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
option: Specifies whether to return the offset of the first character of the match (0) or the offset of the first character following the end of the match (1). Default: 0
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
REGEXP_LIKE(subject, pattern [, parameters ])
Returns true if the subject matches the pattern. Both expressions must be text expressions.
subject: Subject to match.
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
REGEXP_REPLACE(subject, pattern [, replacement [, position [, occurrence [, parameters]]]])
Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. If no matches are found, returns the original subject.
subject: Subject to match.
pattern: Pattern to match.
replacement: String the replaces the substrings matched by the pattern. If an empty string is specified, the function removes all matched patterns and returns the resulting string. Default: '' (empty string).
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
occurrence: Specifies which occurrence of the pattern to replace. If 0 is specified, all occurrences are replaced. Default: 0 (all occurrences)
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
REGEXP_SUBSTR(subject, pattern [, position, occurrence, parameters])
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
RLIKE(subject, pattern, parameters)
Returns true if the subject matches the specified pattern. Both inputs must be text expressions.
subject: Subject to match
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
ASCII(input)
Returns the ASCII code for the first character of a string. If the string is empty, a value of 0 is returned.
input: The string for which the ASCII code for the first character in the string is returned.
BIT_LENGTH(string_or_binary)
Returns the length of a string or binary value in bits. Snowflake doesn't use fractional bytes so length is always calculated as 8 * OCTET_LENGTH.
string_or_binary: The string or binary value for which the length is returned.
CHARINDEX(expr1, expr2 [, start_pos ])
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr1). Default: 1
CHAR(input)
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
CHR(input)
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
CONCAT(expr1, expr2)
Concatenates two strings or two binary values. If one of the them is null, the result is also null.
expr1: A string to concatenate
expr2: A string to concatenate
CONTAINS(expr1, expr2)
Returns true if expr1 contains expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
EDITDISTANCE(expr1, expr2)
Computes the Levenshtein distance between two input strings. It is the number of single-character insertions, deletions or substitutions needed to convert one string to another.
expr1: A string
expr2: A string
ENDSWITH(expr1, expr2)
Returns TRUE if the first expression ends with second expression. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
ILIKE(subject, pattern [, escape ])
Allows matching of strings based on comparison with a pattern. Unlike the LIKE function, string matching is case-insensitive. LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
INITCAP(expr [, delimiters ])
Returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.
expr: The input string
delimiters: an optional argument specifying a string of one or more characters that INITCAP uses as separators for words in the input expression:
INSERT(base_expr, pos, len, insert_expr)
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value. This function should not be confused with the INSERT DML command.
base_expr: The string or BINARY expression for which you want to insert/replace characters.
pos: The offset at which to start inserting characters. This is 1-based, not 0-based.
len: The number of characters (starting at pos) that you want to replace. Valid values range from 0 to the number of characters between pos and the end of the string.
insert_expr: The string to insert into the base_expr. If this string is empty, and if len is greater than zero, then effectively the operation becomes a delete (some characters are deleted, and none are added).
LEFT(string_expr, length_expr)
Returns a leftmost substring of its input. LEFT(STR,N) is equivalent to SUBSTR(STR,1,N).
string_expr: The string expression
length_expr: The length to extract
LENGTH(expr)
Returns the length of a input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.
expr: The expression to measure
LIKE(subject, pattern [, escape ])
Allows case-sensitive matching of strings based on comparison with a pattern. For case-insensitive matching, use ILIKE instead.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
LOWER(expr)
Returns the input string (expr) with all characters converted to lowercase.
expr: The input string
LPAD(base, n [, pad])
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
base: The base string to pad
n: The number of characters to bad
pad: The character to pad
LTRIM(expr [, characters])
Removes leading characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the left side of expr. The default value is ' ' (a single blank space character),
OCTET_LENGTH(string_or_binary)
Returns the length of a string or binary value in bytes. This will be the same as LENGTH for ASCII strings and greater than LENGTH for strings using Unicode code points. For binary, this is always the same as LENGTH.
string_or_binary: The string or binary value for which the length is returned.
PARSE_IP(expr, type [, permissive])
Returns a JSON object consisting of all the components from a valid INET (Internet Protocol) or CIDR (Classless Internet Domain Routing) IPv4 or IPv6 string.
expr: A string expression.
type: Identifies the type of IP address. Supports either INET or CIDR; case-insenstitive.
permissive: Flag that determines how parse errors are handled:
PARSE_URL(string [, permissive])
Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI.
string: String to parse.
permissive: Flag that determines how parse errors are handled
POSITION(expr1, expr2 [, start_pos ])
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr2).
POSITION(expr1 IN expr2)
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
REPEAT(input, n)
Builds a string by repeating the input for the specified number of times.
input: The input string from which the output string is built.
n: The number of times the input string should be repeated. The minimum valid number is 0 (which results in an empty string).
REPLACE(subject, pattern [, replacement])
Removes all occurrences of a specified substring, and optionally replaces them with another string
subject: The subject is the string in which to do the replacements. Typically, this is a column, but it can be a literal.
pattern: This is the substring that you want to replace. Typically, this is a literal, but it can be a column or expression. Note that this is not a "regular expression"; if you want to use regular expressions to search for a pattern, use the REGEXP_REPLACE function.
replacement: This is the value used as a replacement for the pattern. If this is omitted, or is an empty string, then the REPLACE function simply deletes all occurrences of the pattern.
REVERSE(subject)
everses the order of characters in a string, or of bytes in a binary value.
subject: The string to reverse
RIGHT(string_expr, length_expr)
Returns a rightmost substring of its input.
string_expr: The string expression
length_expr: The length to extract
RPAD(base, n [, pad])
Right-pads a string or binary value with characters from another string.
base: The base string to pad
n: The number of times to pad
pad: The string to pad with
RTRIM(expr [, characters])
Removes trailing characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the right side of expr:
RTRIMMED_LENGTH(string_expr)
Returns the length of its argument, minus trailing whitespace, but including leading whitespace.
string_expr: The string expression to measure
SPACE(n)
Builds a string consisting of the specified number of blank spaces.
n: The number of blank spaces used to build the string.
SPLIT(string, separator)
Splits a given string with a given separator and returns the result in an array of strings. Contiguous split strings in the source string, or the presence of a split string at the beginning or end of the source string, results in an empty string in the output. An empty separator string results in an array containing only the source string. If either parameter is a NULL, a NULL is returned.
string: Text to be split into parts.
separator: Text to split string by.
SPLIT_TO_TABLE(string, delimiter)
Splits a string with the given delimiter and flattens the results into rows.
string: Text to be split into parts.
delimiter: Text to split string by.
STRTOK(string [, delimiter] [, partNr])
Tokenizes a given string and returns the requested part.
string: Text to be tokenized.
delimiter: Text representing the set of delimiters to tokenize on. Each character in the delimiter string is a delimiter. If the delimiter is empty, and the string is empty, then the function returns NULL. If the delimiter is empty, and the string is non empty, then the whole string will be treated as one token. The default value of the delimiter is a single space character.
partNr: Requested token (1-based, i.e. the first token is token number 1, not token number 0). If the token number is out of range, then NULL is returned. The default value is 1.
SPLIT_PART(string, delimiter, partNr)
Splits a given string and returns the requested part. If a part does not exist, an empty string is returned. If any parameter is NULL, NULL is returned.
string: Text to be split into parts.
delimiter: Text representing the delimiter to split by.
partNr: Requested part of the split (1-based). 0 is treated as 1. If the value is negative, the parts are counted from the right side of the string.
STARTSWITH(expr1, expr2)
Returns true if expr1 starts with expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
SUBSTRING(base_expr, start_expr [, length_expr])
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
COMPRESS(input, method)
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY or string value (or expression) to be compressed.
method: A string with compression method and optional compression level. Supported methods are: SNAPPY. ZLIB. ZSTD. BZ2. The compression level is specified in parentheses, for example: zlib(1). The compression level is a non-negative integer. 0 means default level (same as omitting the compression level). The compression level is ignored if the method doesn't support compression levels.
DECOMPRESS_BINARY(input, method)
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS. If you attempt to decompress a compressed string, rather than a compressed BINARY value, you will not get an error; the function will return a BINARY value. See the Usage Notes below for details.
method: The compression method originally used to compress the input.
DECOMPRESS_STRING(input, method)
Decompresses the compressed BINARY input parameter to a string.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS.
method: The compression method originally used to compress the input.
SUBSTR(base_expr, start_expr [, length_expr])
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
TRANSLATE(subject, sourceAlphabet, targetAlphabet)
Translates subject from the characters in sourceAlphabet to the characters in targetAlphabet.
subject: A string expression that is translated. If a character in subject is not contained in sourceAlphabet, the character is added to the result without any translation.
sourceAlphabet: A string with all characters that are modified by this function. Each character is either translated to the corresponding character in the targetAlphabet or omitted in the result if the targetAlphabet has no corresponding character (i.e. has less characters than the sourceAlphabet).
targetAlphabet: A string with all characters that are used to replace characters from the sourceAlphabet.
TRIM(expr [, characters])
Removes leading and trailing characters from a string.
expr: A string expression to be trimmed.
characters: One or more characters to remove from the left and right side of expr: The default value is ' ' (a single blank space character), i.e. if no characters are specified, all leading and trailing blank spaces are removed.
UNICODE(input)
Returns the Unicode code point for the first Unicode character in a string. If the string is empty, a value of 0 is returned.
input: The string for which the Unicode code point for the first character in the string is returned.
UPPER(expr)
Returns the input string expr with all characters converted to uppercase.
expr: The input string
BASE64_DECODE_BINARY(input [, alphabet])
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
BASE64_DECODE_STRING(input [, alphabet])
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
BASE64_ENCODE(input [, max_line_length ] [, alphabet ] )
Encodes the input (string or binary) using Base64 encoding.
input: A string or binary expression to be encoded.
max_line_length: A positive integer that specifies the maximum number of characters in a single line of the output.
alphabet: A string consisting of up to three ASCII characters
HEX_DECODE_BINARY(input)
Decodes a hex-encoded string to a binary.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
HEX_DECODE_STRING(input)
Decodes a hex-encoded string to a string.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
HEX_ENCODE(input [, case])
Encodes the input using hexadecimal (also 'hex' or 'base16') encoding. The result is comprised of 16 different symbols: The numbers '0' to '9' as well as the letters 'A' to 'F'
input: A binary or string expression to be encoded.
case: This optional boolean argument controls the case of the letters ('A', 'B', 'C', 'D', 'E' and 'F') used in the encoding. The default value is 1 and indicates that uppercase letters are used. The value 0 indicates that lowercase letters are used. All other values are illegal and result in an error.
TRY_BASE64_DECODE_BINARY(input [, alphabet])
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
TRY_BASE64_DECODE_STRING(input [, alphabet])
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
TRY_HEX_DECODE_BINARY(input)
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
TRY_HEX_DECODE_STRING(input)
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
MD5(msg)
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
MD5_HEX(msg)
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed
MD5_BINARY(msg)
Returns a 16-byte BINARY value containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
MD5_NUMBER(msg)
Returns the 128-bit MD5 message digest interpreted as a signed 128-bit big endian number. This representation is useful for maximally efficient storage and comparison of MD5 digests.
msg: A string expression, the message to be hashed.
SHA1(msg)
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
SHA1_HEX(msg)
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
SHA1_BINARY(msg)
Returns a 20-byte binary containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
SHA2(msg [, digest_size])
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
SHA2_HEX(msg [, digest_size])
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
SHA2_BINARY(msg [, digest_size])
Returns a binary containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
ANY_VALUE([DISTINCT] expr)
Returns some value of the expression from the group. The result is non-deterministic.
expr: A group of values to choose from
AVG([DISTINCT] expr)
Returns the average of non-NULL records. If all records inside a group are NULL, NULL is returned.
expr: Expression is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
CORR(y, c)
Returns the correlation coefficient for non-null pairs in a group. It is computed for non-null pairs using the following formula: COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y))
y:
c:
COUNT([DISTINCT] expr1 [, expr2])
Returns either the number of non-NULL records for the specified columns, or a total number of records.
expr1: A column name.
expr2: You may include additional column name(s) if you wish. For example, you could list the number of distinct combinations of last name and first name.
COVAR_POP(x, y)
Returns the population covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*) Where x is the independent variable and y is the dependent variable.
x:
y:
COVAR_SAMP(y, c)
Returns the sample covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / (COUNT(*) - 1) Where x is the independent variable and y is the dependent variable.
y:
x:
LISTAGG([DISTINCT] expr, delimiter)
Returns the concatenated input values, separated by the delimiter string.
expr: The expression (typically a column name) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter: A string, or an expression that evaluates to a string. In practice, this is usually a single-character string. The string should be surrounded by single quotes, as shown in the examples below.
MAX(expr)
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
MIN(expr)
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
MEDIAN(expr)
Determines the median of a set of values.
expr: The expression must evaluate to a numeric data type (INTEGER, FLOAT, DECIMAL, or equivalent).
PERCENTILE_CONT(percentile)
Return a percentile value based on a continuous distribution of the input column (specified in order_by_expr). If no input row lies exactly at the desired percentile, the result is calculated using linear interpolation of the two nearest input values. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
PERCENTILE_DISC(percentile)
Returns a percentile value based on a discrete distribution of the input column (specified in order_by_expr). The returned value is that whose row has the smallest CUME_DIST value that is greater than or equal to the given percentile. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
STDDEV(expr)
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value.
STDDEV_SAMP([DISTINCT] expr)
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value (integer, floating point, or fixed point).
STDDEV_POP([DISTINCT] x)
Returns the population standard deviation (square root of variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
x:
SUM([DISTINCT] expr)
Returns the sum of non-NULL records for expr. If the DISTINCT keyword is used, the sum of unique non-null values is computed. If all records inside a group are NULL, a value of NULL is returned.
expr: The expression to sum
VAR_POP([DISTINCT] x)
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VARIANCE_POP([DISTINCT] x)
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VAR_SAMP([DISTINCT] x)
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VARIANCE([DISTINCT] x)
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
VARIANCE_SAMP([DISTINCT] x)
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
CUME_DIST()
Finds the cumulative distribution of a value with regard to other values within the same window partition.
DENSE_RANK()
Returns the rank of a value within a group of values, without gaps in the ranks. The rank value starts at 1 and continues up sequentially. If two values are the same, they will have the same rank.
FIRST_VALUE(expr)
Returns the first value within an ordered group of values.
expr:
LAG(expr [, offset, default])
Accesses data in a previous row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows backward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr
LAST_VALUE(expr)
Returns the last value within an ordered group of values.
expr:
LEAD(expr, offset, default)
Accesses data in a subsequent row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows forward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr.
NTH_VALUE(expr, n)
Returns the nth value (up to 1000) within an ordered group of values.
expr:
n: Input value n cannot be greater than 1000.
NTILE(constant_value)
Divides an ordered data set equally into the number of buckets specified by constant_value. Buckets are sequentially numbered 1 through constant_value.
constant_value: The desired number of buckets; must be a positive integer value.
PERCENT_RANK()
Returns the relative rank of a value within a group of values.
RANK()
Returns the rank of a value within an ordered group of values.
ROW_NUMBER()
Returns a unique row number for each row within a window partition.
WIDTH_BUCKET(expr, min_value, max_value, num_buckets)
Constructs equi-width histograms, in which the histogram range is divided into intervals of identical size, and returns the bucket number into which the value of an expression falls, after it has been evaluated. The function returns an integer value or null (if any input is null).
expr: The expression for which the histogram is created. This expression must evaluate to a numeric value or to a value that can be implicitly converted to a numeric value.
min_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
max_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
num_buckets: The desired number of buckets; must be a positive integer value. A value from the expression is assigned to each bucket, and the function then returns the corresponding bucket number.
BITAND_AGG(expr)
Returns the bitwise AND value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
BITOR_AGG(expr)
Returns the bitwise OR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
BITXOR_AGG([DISTINCT] expr)
Returns the bitwise XOR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
ARRAY_AGG([DISTINCT] expr)
Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
expr: The expression (typically a column name) that determines the values to be put into the list
REGR_AVGX(y, x)
Returns the average of the independent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
REGR_COUNT(y, x)
Returns the number of non-null number pairs in a group.
y:
x:
REGR_INTERCEPT(y, x)
Returns the intercept of the univariate linear regression line for non-null pairs in a group. It is computed for non-null pairs using the following formula: AVG(y)-REGR_SLOPE(y,x)*AVG(x) Where x is the independent variable and y is the dependent variable
y:
x:
REGR_R2(y, x)
Returns the coefficient of determination for non-null pairs in a group.
y:
x:
REGR_SLOPE(y, x)
Returns the slope of the linear regression line for non-null pairs in a group
y:
x:
REGR_SXX(y, x)
Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs.
y:
x:
REGR_SXY(y, x)
Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs.
y:
x:
REGR_SYY(y, x)
Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs.
y:
x:
REGR_AVGY(y, x)
Returns the average of the dependent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
APPROX_COUNT_DISTINCT([DISTINCT] expr [, ...])
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
HLL([DISTINCT] expr [, ...])
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
HLL_COMBINE([DISTINCT] state)
Combines (merges) input states into a single output state. This allows scenarios where HLL_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using HLL_COMBINE, producing the same output state as a single run of HLL_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE.
HLL_ESTIMATE(state)
Returns the cardinality estimate for the given HyperLogLog state. A HyperLogLog state produced by HLL_ACCUMULATE and HLL_COMBINE can be used to compute a cardinality estimate using the HLL_ESTIMATE function.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE or HLL_COMBINE.
HLL_ACCUMULATE([DISTINCT] expr)
Returns the HyperLogLog state at the end of aggregation.
expr:
HLL_EXPORT(binary_expr)
Converts input in BINARY format to OBJECT format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports converting states from the BINARY format to an OBJECT (which can be printed and exported as JSON), and vice versa.
binary_expr:
HLL_IMPORT(obj)
Converts input in OBJECT format to BINARY format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports using HLL_IMPORT to convert states from an OBJECT format to BINARY, and vice versa.
obj:
APPROXIMATE_JACCARD_INDEX([DISTINCT] expr [, ...])
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
APPROXIMATE_SIMILARITY([DISTINCT] expr [, ...])
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
MINHASH(k, [DISTINCT] expr [, ...])
Returns a MinHash state containing an array of size k constructed by applying k number of different hash functions to the input rows and keeping the minimum of each hash function. This MinHash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with one or more other MinHash states.
k: specifies the number of hash functions to be created. The larger the value, the better the approximation; however, this value has a linear impact on the computation time for estimating similarity using APPROXIMATE_SIMILARITY. The suggested value is 100.
expr:
MINHASH_COMBINE([DISTINCT] state)
Combines input MinHash states into a single MinHash output state. This Minhash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with other MinHash states.
state: Input MinHash state must have MinHash arrays of equal length.
APPROX_TOP_K(expr, k, counters)
Uses Space-Saving to return an approximation of the most frequent values in the input, along with their approximate frequencies. The output is a JSON array of arrays. In the inner arrays, the first entry is a value in the input, and the second entry corresponds to its estimated frequency in the input. The outer array contains k items, sorted by descending frequency.
expr: The expression (e.g. column name) for which you want to find the most common values.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
APPROX_TOP_K_ACCUMULATE(expr, counters)
Returns the Space-Saving summary at the end of aggregation. (For more information about the Space-Saving summary, see Estimating Frequent Values.) The function APPROX_TOP_K discards its internal, intermediate state when the final cardinality estimate is returned. However, in certain advanced use cases, such as estimating incremental frequent values during bulk loading, you might want to keep the intermediate state, in which case you would use APPROX_TOP_K_ACCUMULATE instead of APPROX_TOP_K..
expr: The expression (e.g. column name) for which you want to find the most common values.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values
APPROX_TOP_K_COMBINE(state [, counters])
Combines (merges) input states into a single output state. This allows scenarios where APPROX_TOP_K_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using APPROX_TOP_K_COMBINE, producing the same output state as a single run of APPROX_TOP_K_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
APPROX_TOP_K_ESTIMATE(state [, k])
Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. (For more information about the Space-Saving summary, see Estimating Frequent Values.)
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE or APPROX_TOP_K_COMBINE.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
APPROX_PERCENTILE(expr, percentile)
Returns an approximated value for the desired percentile (i.e. if column c has n numbers, then APPROX_PERCENTILE(c, p) returns a number such that approximately n * p of the numbers in c are smaller than the returned number).
expr: A valid expression, such as a column name, that evaluates to a numeric value.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
APPROX_PERCENTILE_ACCUMULATE(expr)
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. (For more information about t-Digest, see: Estimating Percentile Values.)
expr: A valid expression, such as a column name, that evaluates to a numeric value.
APPROX_PERCENTILE_COMBINE(state)
Combines (merges) percentile input states into a single output state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE.
APPROX_PERCENTILE_ESTIMATE(state, percentile)
Returns the desired approximated percentile value for the specified t-Digest state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE or APPROX_PERCENTILE_COMBINE.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
GROUPING(expr1 [, ...])
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
GROUPING_ID(expr1 [, ...])
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
SYSTEMABORT_SESSION(session_id)
Aborts the specified session.
session_id: Identifier for the session to abort.
SYSTEMABORT_TRANSACTION(transaction_id)
Aborts the specified transaction, if it is running. If the transaction has already been committed or rolled back, then the state of the transaction is not altered.
transaction_id: Identifier for the transaction to abort. To obtain transaction IDs, you can use the SHOW TRANSACTIONS or SHOW LOCKS commands.
SYSTEMCANCEL_ALL_QUERIES(session_id)
Cancels all active/running queries in the specified session.
session_id: Identifier for the session for which to cancel all queries.
SYSTEMCANCEL_QUERY(query_id)
Cancels the specified query (or statement) if it is currently active/running.
query_id: Identifier for the query to cancel.
SYSTEMPIPE_FORCE_RESUME(pipe_name)
Forces a pipe paused using ALTER PIPE to resume. This is necessary if the pipe owner transfers ownership of the pipe to another role while the pipe is paused.
pipe_name: Pipe to resume running.
SYSTEMWAIT(amount [, time_unit])
Waits for a specified amount of time before proceeding.
amount: Number specifying the amount of time to wait as determined by time_unit.
time_unit: Time unit for amount. Accepted values are DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS. The unit should be in single quotes
SYSTEMLAST_CHANGE_COMMIT_TIME(object_name)
Returns the commit time of the last DML change performed on a table or a view. In case of a view, the function returns the latest commit time of all the objects referenced in the view.
object_name: ecifies the table or view for which the commit time for the last DML is returned
SYSTEMPIPE_STATUS(pipe_name)
Retrieves a JSON representation of the current status of a pipe.
pipe_name: Pipe for which you want to retrieve the current status.
SYSTEMTYPEOF(expr)
Returns a string representing the SQL data type associated with an expression.
expr:
Last updated