# All Functions (Alphabetical)¶

This topic provides a list of all Snowflake system-defined (i.e. built-in) functions, scalar or table, in alphabetical order.

The list includes:

• The name of each function.

• A summary of each function.

• A list of the categories that the function belongs in.

Function Name

Summary

Category

A

ABS

Returns the absolute value of a numeric expression.

Numeric Functions

ACOS

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

Numeric Functions

ACOSH

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

Numeric Functions

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

Date & Time Functions

ALL_USER_NAMES

Returns all user names in the current account.

Context Functions

ANY_VALUE

Returns some value of the expression from the group.

APPROX_COUNT_DISTINCT

Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e. HLL(col1, col2, ... ) returns an approximation of COUNT(DISTINCT col1, col2, ... )).

APPROX_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).

APPROX_PERCENTILE_ACCUMULATE

Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation.

APPROX_PERCENTILE_COMBINE

Combines (merges) percentile input states into a single output state.

APPROX_PERCENTILE_ESTIMATE

Returns the desired approximated percentile value for the specified t-Digest state.

APPROX_TOP_K

Uses Space-Saving to return an approximation of the most frequent values in the input, along with their approximate frequencies.

APPROX_TOP_K_ACCUMULATE

Returns the Space-Saving summary at the end of aggregation.

APPROX_TOP_K_COMBINE

Combines (merges) input states into a single output state.

APPROX_TOP_K_ESTIMATE

Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state.

APPROXIMATE_JACCARD_INDEX

Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states.

APPROXIMATE_SIMILARITY

Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states.

ARRAY_AGG

Returns the input values, pivoted into an ARRAY.

ARRAY_APPEND

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

Semi-structured Data Functions

ARRAY_CAT

Returns a concatenation of two arrays.

Semi-structured Data Functions

ARRAY_COMPACT

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

Semi-structured Data Functions

ARRAY_CONSTRUCT

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

Semi-structured Data Functions

ARRAY_CONSTRUCT_COMPACT

Returns an array constructed from zero, one, or more inputs; the constructed array omits any NULL input values.

Semi-structured Data Functions

ARRAY_CONTAINS

Returns True if the specified variant is found in the specified array.

Semi-structured Data Functions

ARRAY_DISTINCT

Returns a new ARRAY that contains only the distinct elements from the input ARRAY.

Semi-structured Data Functions

ARRAY_EXCEPT

Returns a new ARRAY that contains the elements from one input ARRAY that are not in another input ARRAY.

Semi-structured Data Functions

ARRAY_INSERT

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

Semi-structured Data Functions

ARRAY_INTERSECTION

Returns an array that contains the matching elements in the two input arrays.

Semi-structured Data Functions

ARRAY_POSITION

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

Semi-structured Data Functions

ARRAY_PREPEND

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

Semi-structured Data Functions

ARRAY_SIZE

Returns the size of the input array.

Semi-structured Data Functions

ARRAY_SLICE

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

Semi-structured Data Functions

ARRAY_TO_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).

Semi-structured Data Functions

ARRAY_UNION_AGG

Returns an ARRAY that contains the union of the distinct values from the input ARRAYs in a column.

ARRAY_UNIQUE_AGG

Returns an ARRAY that contains all of the distinct values from the specified column.

ARRAYS_OVERLAP

Compares whether two arrays have at least one element in common.

Semi-structured Data Functions

AS_<object_type>

This family of functions can be used to perform strict casting of VARIANT values to other data types.

Semi-structured Data Functions

AS_ARRAY

Casts a VARIANT value to an array.

Semi-structured Data Functions

AS_BINARY

Casts a VARIANT value to a binary string.

Semi-structured Data Functions

AS_BOOLEAN

Casts a VARIANT value to a BOOLEAN value.

Semi-structured Data Functions

AS_CHAR , AS_VARCHAR

Casts a VARIANT value to a string.

Semi-structured Data Functions

AS_DATE

Casts a VARIANT value to a date.

Semi-structured Data Functions

AS_DECIMAL , AS_NUMBER

Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.

Semi-structured Data Functions

AS_DOUBLE , AS_REAL

Casts a VARIANT value to a floating-point value.

Semi-structured Data Functions

AS_INTEGER

Casts a VARIANT value to an integer.

Semi-structured Data Functions

AS_OBJECT

Casts a VARIANT value to an object.

Semi-structured Data Functions

AS_TIME

Casts a VARIANT value to a time value.

Semi-structured Data Functions

AS_TIMESTAMP_*

Casts a VARIANT value to the respective TIMESTAMP value.

Semi-structured Data Functions

ASCII

Returns the ASCII code for the first character of a string.

String & Binary Functions

ASIN

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

Numeric Functions

ASINH

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

Numeric Functions

ATAN

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

Numeric Functions

ATAN2

Computes the inverse tangent (arc tangent) of the ratio of its two arguments.

Numeric Functions

ATANH

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

Numeric Functions

AUTO_REFRESH_REGISTRATION_HISTORY

This table function can be used to query the history of data files registered in the metadata of specified objects and the credits billed for these operations.

AUTOMATIC_CLUSTERING_HISTORY

This table function is used for querying the Automatic Clustering history for given tables within a specified date range.

AVG

Returns the average of non-NULL records.

B

BASE64_DECODE_BINARY

Decodes a Base64-encoded string to a binary.

String & Binary Functions

BASE64_DECODE_STRING

Decodes a Base64-encoded string to a string.

String & Binary Functions

BASE64_ENCODE

Encodes the input (string or binary) using Base64 encoding.

String & Binary Functions

[ NOT ] BETWEEN

Returns TRUE when the input expression (numeric or string) is within the specified lower and upper boundary.

Conditional Expression Functions

BIT_LENGTH

Returns the length of a string or binary value in bits.

String & Binary Functions

BITAND

Returns the bitwise AND of two numeric expressions.

Bitwise Expression Functions

BITAND_AGG

Returns the bitwise AND value of all non-NULL numeric records in a group.

BITMAP_BIT_POSITION

Given a numeric value, returns the relative position for the bit that represents that value in a bitmap.

Aggregate Functions

BITMAP_BUCKET_NUMBER

Given a numeric value, returns an identifier (“bucket number”) for the bitmap containing the bit that represents the value..

Aggregate Functions

BITMAP_CONSTRUCT_AGG

Returns a bitmap with bits set for each distinct value in a group.

Aggregate Functions

BITMAP_COUNT

Given a bitmap that represents the set of distinct values for a column, returns the number of distinct value.

Aggregate Functions

BITMAP_OR_AGG

Returns a bitmap containing the results of a binary OR operation on the input bitmaps.

Aggregate Functions

BITNOT

Returns the bitwise negation of a numeric expression.

Bitwise Expression Functions

BITOR

Returns the bitwise OR of two numeric expressions.

Bitwise Expression Functions

BITOR_AGG

Returns the bitwise OR value of all non-NULL numeric records in a group.

BITSHIFTLEFT

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

Bitwise Expression Functions

BITSHIFTRIGHT

Shifts the bits for a numeric expression n positions to the right.

Bitwise Expression Functions

BITXOR

Returns the bitwise XOR of two numeric expressions.

Bitwise Expression Functions

BITXOR_AGG

Returns the bitwise XOR value of all non-NULL numeric records in a group.

BOOLAND

Computes the Boolean AND of two numeric expressions.

Conditional Expression Functions

BOOLAND_AGG

Returns the logical (boolean) AND value of all non-NULL boolean records in a group.

BOOLNOT

Computes the Boolean NOT of a single numeric expression.

Conditional Expression Functions

BOOLOR

Computes the Boolean OR of two numeric expressions.

Conditional Expression Functions

BOOLOR_AGG

Returns the logical (boolean) OR value of all non-NULL boolean records in a group.

BOOLXOR

Computes the Boolean XOR of two numeric expressions (i.e. one of the expressions, but not both expressions, is TRUE).

Conditional Expression Functions

BOOLXOR_AGG

Returns the logical (boolean) XOR value of all non-NULL boolean records in a group.

BUILD_SCOPED_FILE_URL

Generates a scoped Snowflake-hosted URL to a staged file using the stage name and relative file path as inputs.

File Functions

BUILD_STAGE_FILE_URL

Generates a Snowflake-hosted file URL to a staged file using the stage name and relative file path as inputs.

File Functions

C

CASE

Works like a cascading “if-then-else” statement.

Conditional Expression Functions

CAST , ::

Converts a value of one data type into another data type.

Conversion Functions

CBRT

Returns the cubic root of a numeric expression.

Numeric Functions

CEIL

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.

Numeric Functions

CHARINDEX

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.

String & Binary Functions

CHECK_JSON

Checks the validity of a JSON document.

Semi-structured Data Functions

CHECK_XML

Checks the validity of an XML document.

Semi-structured Data Functions

CHR , CHAR

Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode.

String & Binary Functions

COALESCE

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

Conditional Expression Functions

COLLATE

Returns a copy of the original string, but with the specified collation_specification property instead of the original collation_specification property.

String & Binary Functions

COLLATION

Returns the collation specification of the expression.

String & Binary Functions

Returns the status of a completed graph run.

COMPRESS

Compresses the input string or binary value with a compression method.

String & Binary Functions

CONCAT , ||

Concatenates one or more strings, or concatenates one or more binary values.

String & Binary Functions

CONCAT_WS

Concatenates two or more strings, or concatenates two or more binary values.

String & Binary Functions

CONDITIONAL_CHANGE_EVENT

Returns a window event number for each row within a window partition when the value of the argument expr1 in the current row is different from the value of expr1 in the previous row.

Window Functions

CONDITIONAL_TRUE_EVENT

Returns a window event number for each row within a window partition based on the result of the boolean argument expr1.

Window Functions

CONTAINS

Returns true if expr1 contains expr2.

String & Binary Functions

CONVERT_TIMEZONE

Converts a timestamp to another time zone.

Date & Time Functions

COPY_HISTORY

This table function can be used to query Snowflake data loading history along various dimensions within the last 14 days.

CORR

Returns the correlation coefficient for non-null pairs in a group.

COS

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

Numeric Functions

COSH

Computes the hyperbolic cosine of its argument.

Numeric Functions

COT

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

Numeric Functions

COUNT

Returns either the number of non-NULL records for the specified columns, or the total number of records.

COUNT_IF

Returns the number of records that satisfy a condition.

COVAR_POP

Returns the population covariance for non-null pairs in a group.

COVAR_SAMP

Returns the sample covariance for non-null pairs in a group.

CUME_DIST

Finds the cumulative distribution of a value with regard to other values within the same window partition.

Window Functions

CURRENT_ACCOUNT

Returns the account locator used by the user’s current session.

Context Functions

CURRENT_AVAILABLE_ROLES

Returns a string (VARCHAR) that lists all roles granted to the current user.

Context Functions

CURRENT_CLIENT

Returns the version of the client from which the function was called.

Context Functions

CURRENT_DATABASE

Returns the name of the database in use for the current session.

Context Functions

CURRENT_DATE

Returns the current date of the system.

Context Functions

Returns the IP address of the client that submitted the request.

Context Functions

CURRENT_REGION

Returns the name of the region for the account where the current user is logged in.

Context Functions

CURRENT_ROLE

Returns the name of the primary role in use for the current session.

Context Functions

CURRENT_SCHEMA

Returns the name of the schema in use by the current session.

Context Functions

CURRENT_SCHEMAS

Returns active search path schemas.

Context Functions

CURRENT_SECONDARY_ROLES

Returns the name of the secondary roles in use for the current session.

Context Functions

CURRENT_SESSION

Returns a unique system identifier for the Snowflake session corresponding to the present connection.

Context Functions

CURRENT_STATEMENT

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

Context Functions

Returns the status of a graph run that is currently scheduled or is executing.

CURRENT_TIME

Returns the current time for the system.

Context Functions

CURRENT_TIMESTAMP

Returns the current timestamp for the system.

Context Functions

CURRENT_TRANSACTION

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

Context Functions

CURRENT_USER

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

Context Functions

CURRENT_VERSION

Returns the current Snowflake version.

Context Functions

CURRENT_WAREHOUSE

Returns the name of the warehouse in use for the current session.

Context Functions

D

DATA_TRANSFER_HISTORY

This table function can be used to query the history of data transferred from Snowflake tables into a different cloud storage provider’s network (i.e. from Snowflake on AWS, Google Cloud Platform, or Microsoft Azure into the other cloud provider’s network) and/or geographical region within a specified date range.

DATABASE_REFRESH_HISTORY

Returns the refresh history for a secondary database.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

The DATABASE_REFRESH_PROGRESS family of functions can be used to query the status of a database refresh along various dimensions.

DATABASE_STORAGE_USAGE_HISTORY

This table function can be used to query the average daily storage usage, in bytes, for a single database (or all the databases in your account) within a specified date range.

DATE_FROM_PARTS

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

Date & Time Functions

DATE_PART

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

Date & Time Functions

DATE_TRUNC

Truncates a DATE, TIME, or TIMESTAMP to the specified precision.

Date & Time Functions

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

Date & Time Functions

DATEDIFF

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

Date & Time Functions

DAYNAME

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

Date & Time Functions

DECODE

Compares the select expression to each search expression in order.

Conditional Expression Functions

DECOMPRESS_BINARY

Decompresses the compressed BINARY input parameter.

String & Binary Functions

DECOMPRESS_STRING

Decompresses the compressed BINARY input parameter to a string.

String & Binary Functions

DECRYPT

Decrypts a BINARY value using a VARCHAR passphrase.

Encryption Functions

DECRYPT_RAW

Decrypts a BINARY value using a BINARY key.

Encryption Functions

DEGREES

Numeric Functions

DENSE_RANK

Returns the rank of a value within a group of values, without gaps in the ranks.

Window Functions

DIV0

Performs division like the division operator (/), but returns 0 when the divisor is 0 (rather than reporting an error).

Numeric Functions

DIV0NULL

Performs division like the division operator (/), but returns 0 when the divisor is 0 or NULL (rather than reporting an error or returning NULL).

Numeric Functions

E

EDITDISTANCE

Computes the Levenshtein distance between two input strings.

String & Binary Functions

ENCRYPT

Encrypts a VARCHAR or BINARY value using a VARCHAR passphrase.

Encryption Functions

ENCRYPT_RAW

Encrypts a BINARY value using a BINARY key.

Encryption Functions

ENDSWITH

Returns TRUE if the first expression ends with second expression.

String & Binary Functions

EQUAL_NULL

Compares whether two expressions are equal.

Conditional Expression Functions

EXP

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

Numeric Functions

EXPLAIN_JSON

This function converts an EXPLAIN plan from JSON to a table.

System Functions

EXTERNAL_FUNCTIONS_HISTORY

This table function retrieves the history of external functions called by Snowflake for your entire Snowflake account.

EXTERNAL_TABLE_FILES

This table function can be used to query information about the staged data files included in the metadata for a specified external table.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

This table function can be used to query information about the metadata history for an external table.

EXTRACT

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

Date & Time Functions

EXTRACT_SEMANTIC_CATEGORIES

Returns a set of categories (semantic and privacy) for each supported column in the specified table or view.

F

FACTORIAL

Computes the factorial of its input.

Numeric Functions

FIRST_VALUE

Returns the first value within an ordered group of values.

Window Functions

FLATTEN

Flattens (explodes) compound values into multiple rows.

FLOOR

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.

Numeric Functions

G

GENERATE_COLUMN_DESCRIPTION

Generates a list of columns from a set of staged files that contain semi-structured data using the INFER_SCHEMA function output.

GENERATOR

Creates rows of data based either on a specified number of rows, a specified generation period (in seconds), or both.

Table Functions

GET

Extracts a value from an ARRAY or an OBJECT (or a VARIANT that contains an ARRAY or OBJECT).

Semi-structured Data Functions

GET_ABSOLUTE_PATH

Retrieves the absolute path of a staged file using the stage name and path of the file relative to its location in the stage as inputs.

File Functions

GET_DDL

Returns a DDL statement that can be used to recreate the specified object.

GET_IGNORE_CASE

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

Semi-structured Data Functions

GET_OBJECT_REFERENCES

Returns a list of objects that a specified object references.

Table Functions

GET_PATH , :

Extracts a value from semi-structured data using a path name.

Semi-structured Data Functions

GET_PRESIGNED_URL

Generates the pre-signed URL to a staged file using the stage name and relative file path as inputs.

File Functions

GET_RELATIVE_PATH

Extracts the path of a staged file relative to its location in the stage using the stage name and absolute file path in cloud storage as inputs.

File Functions

GET_STAGE_LOCATION

Retrieves the URL for an external or internal named stage using the stage name as the input.

File Functions

GETBIT

Given an INTEGER value, returns the value of a bit at a specified position.

Bitwise Expression Functions

GREATEST

Returns the largest value from a list of expressions.

Conditional Expression Functions

GROUPING

Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.

Aggregate Functions

GROUPING_ID

Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.

Aggregate Functions

H

HASH

Returns a signed 64-bit hash value.

Hash Functions

HASH_AGG

Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows.

HAVERSINE

Calculates the great circle distance in kilometers between two points on the Earth’s surface, using the Haversine formula.

Numeric Functions

HEX_DECODE_BINARY

Decodes a hex-encoded string to a binary.

String & Binary Functions

HEX_DECODE_STRING

Decodes a hex-encoded string to a string.

String & Binary Functions

HEX_ENCODE

Encodes the input using hexadecimal (also ‘hex’ or ‘base16’) encoding.

String & Binary Functions

HLL

Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e. HLL(col1, col2, ... ) returns an approximation of COUNT(DISTINCT col1, col2, ... )).

HLL_ACCUMULATE

Returns the HyperLogLog state at the end of aggregation.

HLL_COMBINE

Combines (merges) input states into a single output state.

HLL_ESTIMATE

Returns the cardinality estimate for the given HyperLogLog state.

HLL_EXPORT

Converts input in BINARY format to OBJECT format.

HLL_IMPORT

Converts input in OBJECT format to BINARY format.

HOUR / MINUTE / SECOND

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

Date & Time Functions

I

IFF

Single-level if-then-else expression.

Conditional Expression Functions

IFNULL

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

Conditional Expression Functions

ILIKE

Allows matching of strings based on comparison with a pattern.

String & Binary Functions

ILIKE ANY

Allows case-insensitive matching of strings based on comparison with one or more patterns.

String & Binary Functions

[ NOT ] IN

Tests whether its argument is or is not one of the members of an explicit list or the result of a subquery.

Conditional Expression Functions

INFER_SCHEMA

Automatically detects the file metadata schema in a set of staged data files that contain semi-structured data and retrieves the column definitions.

Table Functions

INITCAP

Returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.

String & Binary Functions

INSERT

Replaces a substring of the specified length, starting at the specified position, with a new string or binary value.

String & Binary Functions

INVOKER_ROLE

Returns the role name of the object executing the query.

Context Functions

INVOKER_SHARE

Returns the name of the share that directly accessed the table or view where the INVOKER_SHARE function is invoked, otherwise the function returns NULL.

Context Functions

IS [ NOT ] DISTINCT FROM

Compares whether two expressions are equal (or not equal).

Conditional Expression Functions

IS [ NOT ] NULL

Determines whether an expression is NULL or is not NULL.

Conditional Expression Functions

IS_<object_type>

This family of functions serves as Boolean predicates that can be used to determine the data type of a value stored in a VARIANT column.

Semi-structured Data Functions

IS_ARRAY

Returns TRUE if its VARIANT argument contains an ARRAY value.

Semi-structured Data Functions

IS_BINARY

Returns TRUE if its VARIANT argument contains a binary string.

Semi-structured Data Functions

IS_BOOLEAN

Returns TRUE if its VARIANT argument contains a Boolean value.

Semi-structured Data Functions

IS_CHAR , IS_VARCHAR

Returns TRUE if its VARIANT argument contains a string value.

Semi-structured Data Functions

IS_DATE , IS_DATE_VALUE

Verifies whether a VARIANT value contains a DATE value.

Semi-structured Data Functions

IS_DECIMAL

Returns TRUE if its VARIANT argument contains a fixed-point decimal value or integer.

Semi-structured Data Functions

IS_DOUBLE , IS_REAL

Returns TRUE if its VARIANT argument contains a floating-point value, fixed-point decimal, or integer.

Semi-structured Data Functions

IS_GRANTED_TO_INVOKER_ROLE

Returns TRUE if the role returned by the INVOKER_ROLE function inherits the privileges of the specified role in the argument.

Context Functions

IS_INTEGER

Returns TRUE if its VARIANT argument contains an integer value.

Semi-structured Data Functions

IS_NULL_VALUE

Returns true if its VARIANT argument is a JSON null value.

IS_OBJECT

Returns TRUE if its VARIANT argument contains an OBJECT value.

Semi-structured Data Functions

IS_ROLE_IN_SESSION

Verifies whether the argument value (i.e. role) is in the user’s active primary or secondary role hierarchy for the session or if the specified column contains a role that is in the user’s active primary or secondary role hierarchy for the session.

Context Functions

IS_TIME

Verifies whether a VARIANT value contains a TIME value.

Semi-structured Data Functions

IS_TIMESTAMP_*

Verifies whether a VARIANT value contains the respective TIMESTAMP value.

Semi-structured Data Functions

J

JAROWINKLER_SIMILARITY

Computes the Jaro-Winkler similarity between two input strings.

String & Binary Functions

JSON_EXTRACT_PATH_TEXT

Parses the first argument as a JSON string and returns the value of the element pointed to by the path in the second argument.

Semi-structured Data Functions

K

KURTOSIS

Returns the population excess kurtosis of non-NULL records.

L

LAG

Accesses data in a previous row in the same result set without having to join the table to itself.

Window Functions

LAST_DAY

Returns the last day of the specified date part for a date or timestamp.

Date & Time Functions

LAST_QUERY_ID

Returns the ID of a specified query in the current session.

Context Functions

LAST_TRANSACTION

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

Context Functions

LAST_VALUE

Returns the last value within an ordered group of values.

Window Functions

Accesses data in a subsequent row in the same result set without having to join the table to itself.

Window Functions

LEAST

Returns the smallest value from a list of expressions.

Conditional Expression Functions

LEFT

Returns a leftmost substring of its input.

String & Binary Functions

LENGTH, LEN

Returns the length of an input string or binary value.

String & Binary Functions

LIKE

Allows case-sensitive matching of strings based on comparison with a pattern.

String & Binary Functions

LIKE ALL

Allows case-sensitive matching of strings based on comparison with one or more patterns.

String & Binary Functions

LIKE ANY

Allows case-sensitive matching of strings based on comparison with one or more patterns.

String & Binary Functions

LISTAGG

Returns the concatenated input values, separated by the delimiter string.

LN

Returns the natural logarithm of a numeric expression.

Numeric Functions

LOCALTIME

Returns the current time for the system.

Context Functions

LOCALTIMESTAMP

Returns the current timestamp for the system.

Context Functions

LOG

Returns the logarithm of a numeric expression.

Numeric Functions

The LOGIN_HISTORY family of table functions can be used to query login attempts by Snowflake users along various dimensions.

LOWER

Returns the input string (expr) with all characters converted to lowercase.

String & Binary Functions

Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.

String & Binary Functions

LTRIM

Removes leading characters, including whitespace, from a string.

String & Binary Functions

M

MATERIALIZED_VIEW_REFRESH_HISTORY

This table function is used for querying the materialized views refresh history for a specified materialized view within a specified date range.

MD5 , MD5_HEX

Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.

String & Binary Functions

MD5_BINARY

Returns a 16-byte BINARY value containing the 128-bit MD5 message digest.

String & Binary Functions

MD5_NUMBER — Obsoleted

Returns the 128-bit MD5 message digest interpreted as a signed 128-bit big endian number.

String & Binary Functions

MD5_NUMBER_LOWER64

Calculates the 128-bit MD5 message digest, interprets it as a signed 128-bit big endian number, and returns the lower 64 bits of the number as an unsigned integer.

String & Binary Functions

MD5_NUMBER_UPPER64

Calculates the 128-bit MD5 message digest, interprets it as a signed 128-bit big endian number, and returns the upper 64 bits of the number as an unsigned integer.

String & Binary Functions

MEDIAN

Determines the median of a set of values.

MIN / MAX

Returns the minimum or maximum value for the records within expr.

MINHASH

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.

MINHASH_COMBINE

Combines input MinHash states into a single MinHash output state.

MOD

Returns the remainder of input expr1 divided by input expr2.

Numeric Functions

MODE

Returns the most frequent value for the values within expr1.

MONTHNAME

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

Date & Time Functions

MONTHS_BETWEEN

Returns the number of months between two DATE or TIMESTAMP values.

Date & Time Functions

N

NEXT_DAY

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

Date & Time Functions

NORMAL

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

Data Generation Functions

This table function can be used to query the history of notifications sent through Snowflake.

NTH_VALUE

Returns the nth value (up to 1000) within an ordered group of values.

Window Functions

NTILE

Divides an ordered data set equally into the number of buckets specified by constant_value.

Window Functions

NULLIF

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

Conditional Expression Functions

NULLIFZERO

Returns NULL if the argument evaluates to 0; otherwise, returns the argument.

Conditional Expression Functions

NVL

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

Conditional Expression Functions

NVL2

Returns values depending on whether the first input is NULL.

Conditional Expression Functions

O

OBJECT_AGG

Returns one OBJECT per group.

OBJECT_CONSTRUCT

Returns an OBJECT constructed from the arguments.

Semi-structured Data Functions

OBJECT_CONSTRUCT_KEEP_NULL

Returns an OBJECT constructed from the arguments.

Semi-structured Data Functions

OBJECT_DELETE

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

Semi-structured Data Functions

OBJECT_INSERT

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

Semi-structured Data Functions

OBJECT_KEYS

Returns an array containing the list of keys in the top-most level of the input object.

Semi-structured Data Functions

OBJECT_PICK

Returns a new OBJECT containing some of the key-value pairs from an existing object.

Semi-structured Data Functions

OCTET_LENGTH

Returns the length of a string or binary value in bytes.

String & Binary Functions

P

PARSE_IP

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.

String & Binary Functions

PARSE_JSON

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

Semi-structured Data Functions

PARSE_URL

Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI.

String & Binary Functions

PARSE_XML

Interprets an input string as an XML document, producing an OBJECT value.

Semi-structured Data Functions

PERCENT_RANK

Returns the relative rank of a value within a group of values, specified as a percentage ranging from 0.0 to 1.0.

Window Functions

PERCENTILE_CONT

Return a percentile value based on a continuous distribution of the input column (specified in order_by_expr).

PERCENTILE_DISC

Returns a percentile value based on a discrete distribution of the input column (specified in order_by_expr).

PI

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

Numeric Functions

PIPE_USAGE_HISTORY

This table function can be used to query the history of data loaded into Snowflake tables using Snowpipe within a specified date range.

POLICY_CONTEXT

Simulates the query result for table or view columns protected by a masking policy, a table or view protected by a row access policy, or both if the table or view is protected by both types of policies.

Context Functions

POLICY_REFERENCES

Returns a row for each object that has the specified policy assigned to the object or returns a row for each policy assigned to the specified object.

POSITION

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.

String & Binary Functions

POW, POWER

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

Numeric Functions

PREVIOUS_DAY

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

Date & Time Functions

Q

QUERY_ACCELERATION_HISTORY

The QUERY_ACCELERATION_HISTORY function is used for querying the query acceleration service history within a specified date range.

QUERY_HISTORY , QUERY_HISTORY_BY_*

The QUERY_HISTORY family of table functions can be used to query Snowflake query history along various dimensions.

R

Numeric Functions

RANDOM

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

Data Generation Functions

RANDSTR

Returns a random string of specified length.

Data Generation Functions

RANK

Returns the rank of a value within an ordered group of values.

Window Functions

RATIO_TO_REPORT

Returns the ratio of a value within a group to the sum of the values within the group.

Window Functions

REGEXP

Returns true if the subject matches the specified pattern.

String Functions (Regular Expressions)

REGEXP_COUNT

Returns the number of times that a pattern occurs in a string.

String Functions (Regular Expressions)

REGEXP_INSTR

Returns the position of the specified occurrence of the regular expression pattern in the string subject.

String Functions (Regular Expressions)

REGEXP_LIKE

Returns true if the subject matches the specified pattern.

String Functions (Regular Expressions)

REGEXP_REPLACE

Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string.

String Functions (Regular Expressions)

REGEXP_SUBSTR

Returns the substring that matches a regular expression within a string.

String Functions (Regular Expressions)

REGEXP_SUBSTR_ALL

Returns an ARRAY that contains all substrings that match a regular expression within a string.

String Functions (Regular Expressions)

REGR_AVGX

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.

REGR_AVGY

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.

REGR_COUNT

Returns the number of non-null number pairs in a group.

REGR_INTERCEPT

Returns the intercept of the univariate linear regression line for non-null pairs in a group.

REGR_R2

Returns the coefficient of determination for non-null pairs in a group.

REGR_SLOPE

Returns the slope of the linear regression line for non-null pairs in a group.

REGR_SXX

Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs.

REGR_SXY

Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs.

REGR_SYY

Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs.

REGR_VALX

Returns NULL if the first argument is NULL; otherwise, returns the second argument.

Conditional Expression Functions

REGR_VALY

Returns NULL if the second argument is NULL; otherwise, returns the first argument.

Conditional Expression Functions

REPEAT

Builds a string by repeating the input for the specified number of times.

String & Binary Functions

REPLACE

Removes all occurrences of a specified substring, and optionally replaces them with another string.

String & Binary Functions

REPLICATION_GROUP_REFRESH_HISTORY

Returns the replication history for a secondary replication or failover group within the last 14 days.

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

The REPLICATION_GROUP_REFRESH_PROGRESS family of functions can be used to query the status of a replication or failover group refresh.

REPLICATION_GROUP_USAGE_HISTORY

Returns the replication usage history for secondary replication or failover groups within the last 14 days.

REPLICATION_USAGE_HISTORY

This table function can be used to query the replication history for a specified database within a specified date range.

REST_EVENT_HISTORY

Returns a list of SCIM REST API requests made to Snowflake over a specified time interval.

Table Functions

RESULT_SCAN

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table.

Table Functions

REVERSE

Reverses the order of characters in a string, or of bytes in a binary value.

String & Binary Functions

RIGHT

Returns a rightmost substring of its input.

String & Binary Functions

RLIKE

Returns true if the subject matches the specified pattern.

String Functions (Regular Expressions)

ROUND

Returns rounded values for input_expr.

Numeric Functions

ROW_NUMBER

Returns a unique row number for each row within a window partition.

Window Functions

Right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value.

String & Binary Functions

RTRIM

Removes trailing characters, including whitespace, from a string.

String & Binary Functions

RTRIMMED_LENGTH

Returns the length of its argument, minus trailing whitespace, but including leading whitespace.

String & Binary Functions

S

SEARCH_OPTIMIZATION_HISTORY

This table function is used for querying the search optimization service maintenance history for a specified table within a specified date range.

SEQ1 / SEQ2 / SEQ4 / SEQ8

Returns a sequence of monotonically increasing integers, with wrap-around.

Data Generation Functions

This table function is used for querying the serverless task usage history.

SHA1 , SHA1_HEX

Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.

String & Binary Functions

SHA1_BINARY

Returns a 20-byte binary containing the 160-bit SHA-1 message digest.

String & Binary Functions

SHA2 , SHA2_HEX

Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.

String & Binary Functions

SHA2_BINARY

Returns a binary containing the N-bit SHA-2 message digest, where N is the specified output digest size.

String & Binary Functions

SIGN

Returns the sign of its argument.

Numeric Functions

SIN

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

Numeric Functions

SINH

Computes the hyperbolic sine of its argument.

Numeric Functions

SKEW

Returns the sample skewness of non-NULL records.

Aggregate Functions

SOUNDEX

Returns a string that contains a phonetic representation of the input string.

String & Binary Functions

SOUNDEX_P123

Returns a string that contains a phonetic representation of the input string, and retains the Soundex code number for the second letter when the first and second letters use the same number.

String & Binary Functions

SPACE

Builds a string consisting of the specified number of blank spaces.

String & Binary Functions

SPLIT

Splits a given string with a given separator and returns the result in an array of strings.

String & Binary Functions

SPLIT_PART

Splits a given string at a specified character and returns the requested part.

String & Binary Functions

SPLIT_TO_TABLE

This table function splits a string (based on a specified delimiter) and flattens the results into rows.

SQRT

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

Numeric Functions

SQUARE

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

Numeric Functions

ST_AREA

Returns the area of the Polygon(s) in a GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_ASEWKB

Given a value of type GEOGRAPHY or GEOMETRY, return the binary representation of that value in EWKB (extended well-known binary) format.

Geospatial Functions

ST_ASEWKT

Given a value of type GEOGRAPHY or GEOMETRY, return the text (VARCHAR) representation of that value in EWKT (extended well-known text) format.

Geospatial Functions

ST_ASGEOJSON

Given a value of type GEOGRAPHY or GEOMETRY, return the GeoJSON representation of that value.

Geospatial Functions

ST_ASWKB , ST_ASBINARY

Given a value of type GEOGRAPHY or GEOMETRY, return the binary representation of that value in WKB (well-known binary) format.

Geospatial Functions

ST_ASWKT , ST_ASTEXT

Given a value of type GEOGRAPHY or GEOMETRY, return the text (VARCHAR) representation of that value in WKT (well-known text) format.

Geospatial Functions

ST_AZIMUTH

Given two Points that are GEOGRAPHY objects, returns the azimuth (in radians) of the line segment formed by the two points.

Geospatial Functions

ST_CENTROID

Returns the Point representing the geometric center of a GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_COLLECT

There are two forms of ST_COLLECT.

Geospatial Functions

ST_CONTAINS

Returns TRUE if a GEOGRAPHY or GEOMETRY object is completely inside another object of the same type.

Geospatial Functions

ST_COVEREDBY

This returns TRUE if no point in one geospatial object is outside another geospatial object.

Geospatial Functions

ST_COVERS

This returns TRUE if no point in one geospatial object is outside of another geospatial object.

Geospatial Functions

ST_DIFFERENCE

Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the points in the first object that are not in the second object (i.e. the difference between the two objects).

Geospatial Functions

ST_DIMENSION

Given a value of type GEOGRAPHY or GEOMETRY, return the “dimension” of the value.

Geospatial Functions

ST_DISJOINT

Returns TRUE if the two GEOGRAPHY objects or the two GEOMETRY objects are disjoint (i.e. do not share any portion of space).

Geospatial Functions

ST_DISTANCE

Returns the minimum geodesic distance between two GEOGRAPHY or the minimum Euclidean distance between two GEOMETRY objects.

Geospatial Functions

ST_DWITHIN

Returns TRUE if the minimum geodesic distance between two points (two GEOGRAPHY objects) is within the specified distance.

Geospatial Functions

ST_ENDPOINT

Returns the last Point in a LineString.

Geospatial Functions

ST_ENVELOPE

Returns the minimum bounding box (a rectangular “envelope”) that encloses a specified GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_GEOGFROMGEOHASH

Returns a GEOGRAPHY object for the polygon that represents the boundaries of a geohash.

ST_GEOGPOINTFROMGEOHASH

Returns a GEOGRAPHY object for the Point that represents the center of a geohash.

ST_GEOGRAPHYFROMWKB

Parses a WKB (well-known binary) or EWKB (extended well-known binary) input and returns a value of type GEOGRAPHY.

ST_GEOGRAPHYFROMWKT

Parses a WKT (well-known text) or EWKT (extended well-known text) input and returns a value of type GEOGRAPHY.

ST_GEOHASH

Returns the geohash for a GEOGRAPHY object.

Geospatial Functions

ST_GEOMETRYFROMWKB

Parses a WKB (well-known binary) or EWKB (extended well-known binary) input and returns a value of type GEOMETRY.

ST_GEOMETRYFROMWKT

Parses a WKT (well-known text) or EWKT (extended well-known text) input and returns a value of type GEOMETRY.

ST_HAUSDORFFDISTANCE

Returns the discrete Hausdorff distance between two GEOGRAPHY objects.

Geospatial Functions

ST_INTERSECTION

Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the set of points that are common to both input objects (i.e. the intersection of the two objects).

Geospatial Functions

ST_INTERSECTS

Returns TRUE if the two GEOGRAPHY objects or the two GEOMETRY objects intersect (i.e. share any portion of space).

Geospatial Functions

ST_LENGTH

Returns the geodesic length of the LineString(s) in a GEOGRAPHY object or the Euclidean length of the LineString(s) in a GEOMETRY object.

Geospatial Functions

ST_MAKEGEOMPOINT , ST_GEOM_POINT

Constructs a GEOMETRY object that represents a Point with the specified longitude and latitude.

Geospatial Functions

ST_MAKELINE

Constructs a GEOGRAPHY or GEOMETRY object that represents a line connecting the points in the input objects.

Geospatial Functions

ST_MAKEPOINT , ST_POINT

Constructs a GEOGRAPHY object that represents a point with the specified longitude and latitude.

Geospatial Functions

ST_MAKEPOLYGON , ST_POLYGON

Constructs a GEOGRAPHY or GEOGRAPHY object that represents a polygon without holes.

Geospatial Functions

ST_NPOINTS , ST_NUMPOINTS

Returns the number of points in a GEOGRAPHY or GEOGRAPHY object.

Geospatial Functions

ST_PERIMETER

Returns the length of the perimeter of the polygon(s) in a GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_POINTN

Returns a Point at a specified index in a LineString.

Geospatial Functions

ST_SETSRID

Returns a GEOMETRY object that has its SRID (spatial reference system identifier) set to the specified value.

Geospatial Functions

ST_SIMPLIFY

Given an input GEOGRAPHY object that represents a line or polygon, returns a simpler approximation of the object.

Geospatial Functions

ST_SRID

Returns the SRID (spatial reference system identifier) of a GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_STARTPOINT

Returns the first Point in a LineString.

Geospatial Functions

ST_SYMDIFFERENCE

Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the set of points from both input objects that are not part of the intersection of the objects (i.e. the symmetric difference of the two objects).

Geospatial Functions

ST_UNION

Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the combined set of all points from both objects (i.e. the union of the two objects).

Geospatial Functions

ST_WITHIN

Returns true if the the first geospatial object is fully contained by the second geospatial object.

Geospatial Functions

ST_X

Returns the longitude (X coordinate) of a Point represented by a GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_XMAX

Returns the maximum longitude (X coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_XMIN

Returns the minimum longitude (X coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_Y

Returns the latitude (Y coordinate) of a Point represented by a GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_YMAX

Returns the maximum latitude (Y coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object.

Geospatial Functions

ST_YMIN

Returns the minimum latitude (Y coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object.

Geospatial Functions

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

This table function can be used to query information about the metadata history for a directory table.

STAGE_STORAGE_USAGE_HISTORY

This table function can be used to query the average daily data storage usage, in bytes, for all the Snowflake stages in your account within a specified date range.

STARTSWITH

Returns true if expr1 starts with expr2.

String & Binary Functions

STDDEV

Returns the sample standard deviation (square root of sample variance) of non-NULL values.

STDDEV_POP

Returns the population standard deviation (square root of variance) of non-NULL values.

STDDEV_SAMP

Returns the sample standard deviation (square root of sample variance) of non-NULL values.

STRIP_NULL_VALUE

Converts a JSON “null” value to a SQL NULL value.

Semi-structured Data Functions

STRTOK

Tokenizes a given string and returns the requested part.

String & Binary Functions

STRTOK_SPLIT_TO_TABLE

Tokenizes a string with the given set of delimiters and flattens the results into rows.

STRTOK_TO_ARRAY

Tokenizes the given string using the given set of delimiters and returns the tokens as an array.

SUBSTR , SUBSTRING

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.

String & Binary Functions

SUM

Returns the sum of non-NULL records for expr.

SYSDATE

Returns the current timestamp for the system, but in the UTC time zone.

Context Functions

SYSTEM$ABORT_SESSION Aborts the specified session. System Functions SYSTEM$ABORT_TRANSACTION

Aborts the specified transaction, if it is running.

System Functions

SYSTEM$AUTHORIZE_PRIVATELINK Enables Private Connectivity to the Snowflake Service for the current account. System Functions SYSTEM$AUTHORIZE_STAGE_PRIVATELINK_ACCESS

Authorizes Snowflake to access the Microsoft Azure Private Endpoint for Azure Private Endpoints for Internal Stages for the current account.

System Functions

SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS Returns the status of the specified behavior change release bundle for the current account. System Functions SYSTEM$CANCEL_ALL_QUERIES

Cancels all active/running queries in the specified session.

System Functions

SYSTEM$CANCEL_QUERY Cancels the specified query (or statement) if it is currently active/running. System Functions SYSTEM$CLUSTERING_DEPTH

Computes the average depth of the table according to the specified columns (or the clustering key defined for the table).

System Functions

SYSTEM$CLUSTERING_INFORMATION Returns clustering information, including average clustering depth, for a table based on one or more columns in the table. System Functions SYSTEM$CLUSTERING_RATIO — Deprecated

Calculates the clustering ratio for a table, based on one or more columns in the table.

System Functions

SYSTEM$CURRENT_USER_TASK_NAME Returns the name of the task currently executing when invoked from the statement or stored procedure defined by the task. System Functions SYSTEM$DATABASE_REFRESH_HISTORY — Deprecated

Returns a JSON object showing the refresh history for a secondary database.

System Functions

SYSTEM$DATABASE_REFRESH_PROGRESS , SYSTEM$DATABASE_REFRESH_PROGRESS_BY_JOB — Deprecated

The SYSTEM$DATABASE_REFRESH_PROGRESS family of functions can be used to query the status of a database refresh along various dimensions. System Functions SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE

Disables the behavior changes included in the specified release bundle for the current account.

System Functions

SYSTEM$DISABLE_DATABASE_REPLICATION Disable replication for a primary database and any secondary databases linked to it. System Functions SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE

Enables behavior changes included in the specified release bundle for the current account.

System Functions

SYSTEM$ESTIMATE_QUERY_ACCELERATION For a previously executed query, this function returns a JSON object that specifies if the query is eligible to benefit from the query acceleration service. System Functions SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS

Returns the estimated costs of adding search optimization to a given table and configuring specific columns for search optimization.

System Functions

SYSTEM$EXPLAIN_JSON_TO_TEXT This function converts EXPLAIN output from JSON to formatted text. System Functions SYSTEM$EXPLAIN_PLAN_JSON

Given the text of a SQL statement, this function generates the EXPLAIN plan in JSON.

System Functions

SYSTEM$EXTERNAL_TABLE_PIPE_STATUS Retrieves a JSON representation of the current refresh status for the internal (hidden) pipe object associated with an external table. System Functions SYSTEM$GENERATE_SAML_CSR

Generates a certificate signing request (CSR) with the subject set to the subject of the certificate stored in the SAML2 integration and can specify the DN to be used in the CSR.

System Functions

SYSTEM$GENERATE_SCIM_ACCESS_TOKEN Returns a new SCIM access token that is valid for six months. System Functions SYSTEM$GET_AWS_SNS_IAM_POLICY

Returns an AWS IAM policy statement that must be added to the Amazon SNS topic policy in order to grant the Amazon SQS messaging queue created by Snowflake to subscribe to the topic.

System Functions

SYSTEM$GET_PREDECESSOR_RETURN_VALUE Retrieves the return value for the predecessor task in a DAG of tasks. System Functions SYSTEM$GET_PRIVATELINK

Verifies whether your current account is authorized for Private Connectivity to the Snowflake Service.

System Functions

SYSTEM$GET_PRIVATELINK_AUTHORIZED_ENDPOINTS Returns a list of the authorized endpoints for your current account to use with Private Connectivity to the Snowflake Service. System Functions SYSTEM$GET_PRIVATELINK_CONFIG

Returns a JSON representation of the Snowflake account information necessary to facilitate the self-service configuration of private connectivity to the Snowflake service or internal stages.

System Functions

SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO Returns the IDs of the virtual network in which your Snowflake account is located. System Functions SYSTEM$GET_TAG

Returns the tag value associated with the specified Snowflake object or column.

System Functions

SYSTEM$GET_TAG_ALLOWED_VALUES Returns a comma-separated list of string values that can be set on a supported object, or NULL to indicate the tag key does not have any specified string values and accepts all possible string values. System Functions SYSTEM$GET_TAG_ON_CURRENT_COLUMN

Returns the tag string value assigned to the column based upon the specified tag or NULL if a tag is not assigned to the specified column.

System Functions

SYSTEM$GET_TAG_ON_CURRENT_TABLE Returns the tag string value assigned to the table based upon the specified tag or NULL if a tag is not assigned to the specified table. System Functions SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER

Enables replication and failover features for a specified account in an organization.

System Functions

SYSTEM$LAST_CHANGE_COMMIT_TIME Returns a token that can be used to detect whether a database table or view changed between two calls to the function. System Functions SYSTEM$LINK_ACCOUNT_OBJECTS_BY_NAME

Adds a global identifier to account objects in the target (current) account that were created using scripts and that match objects with the same names in the source account.

System Functions

SYSTEM$MIGRATE_SAML_IDP_REGISTRATION Migrates an existing SAML identity provider (i.e. IdP) configuration as defined by the account parameter SAML_IDENTITY_PROVIDER to a security integration. System Functions SYSTEM$PIPE_FORCE_RESUME

Forces a pipe paused using ALTER PIPE to resume.

System Functions

SYSTEM$PIPE_STATUS Retrieves a JSON representation of the current status of a pipe. System Functions SYSTEM$REVOKE_PRIVATELINK

Disables Private Connectivity to the Snowflake Service for the current account.

System Functions

SYSTEM$REVOKE_STAGE_PRIVATELINK_ACCESS Revokes the authorization for Snowflake to access the Microsoft Azure Private Endpoint for Azure Private Endpoints for Internal Stages for the current account. System Functions SYSTEM$SET_RETURN_VALUE

Explicitly sets the return value for a task.

System Functions

SYSTEM$SHOW_OAUTH_CLIENT_SECRETS Returns the client secrets in a string. System Functions SYSTEM$STREAM_BACKLOG

Returns the set of table versions between the current offset for a specified stream and the current timestamp.

Table Functions

SYSTEM$STREAM_GET_TABLE_TIMESTAMP Returns the timestamp in nanoseconds of the latest table version at or before the current offset for the specified stream. System Functions SYSTEM$STREAM_HAS_DATA

Indicates whether a specified stream contains change data capture (CDC) records.

System Functions

SYSTEM$TASK_DEPENDENTS_ENABLE Recursively resumes all dependent tasks tied to a specified root task. System Functions SYSTEM$TYPEOF

Returns a string representing the SQL data type associated with an expression.

System Functions

SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS Aborts a run of the specified task that the system has already started to process (i.e. a run with an EXECUTING state in the TASK_HISTORY output). System Functions SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN

Determines whether your External OAuth access token is valid or has expired and needs to be regenerated.

System Functions

SYSTEM$WAIT Waits for the specified amount of time before proceeding. System Functions SYSTEM$WHITELIST — Deprecated

Returns hostnames and port numbers to add to your firewall’s allowed list so that you can access Snowflake from behind your firewall.

System Functions

Returns hostnames and port numbers for AWS PrivateLink, Azure Private Link, and Google Cloud Private Service Connect deployments to add to your firewall’s allowed list so that you can access Snowflake from behind your firewall.

System Functions

T

TAG_REFERENCES

Returns a table in which each row displays an association between a tag and value.

TAG_REFERENCES_ALL_COLUMNS

Returns a table in which each row displays the tag name and tag value assigned to a specific column.

TAG_REFERENCES_WITH_LINEAGE

Returns a table in which each row displays an association between the specified tag and the Snowflake object to which the tag is associated.

TAN

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

Numeric Functions

TANH

Computes the hyperbolic tangent of its argument.

Numeric Functions

This table function returns the list of child tasks for a given root task in a DAG of tasks.

This table function can be used to query the history of task usage within a specified date range.

TIME_FROM_PARTS

Creates a time from individual numeric components.

Date & Time Functions

TIME_SLICE

Calculates the beginning or end of a “slice” of time, where the length of the slice is a multiple of a standard unit of time (minute, hour, day, etc.).

Date & Time Functions

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

Date & Time Functions

TIMEDIFF

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

Date & Time Functions

TIMESTAMP_FROM_PARTS

Creates a timestamp from individual numeric components.

Date & Time Functions

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

Date & Time Functions

TIMESTAMPDIFF

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

Date & Time Functions

TO_ARRAY

Converts the input expression to an ARRAY.

TO_BINARY

Converts the input expression to a binary value.

Conversion Functions

TO_BOOLEAN

Coverts the input text or numeric expression to a Boolean value.

Conversion Functions

TO_CHAR , TO_VARCHAR

Converts the input expression to a string.

Conversion Functions

TO_DATE , DATE

Converts an input expression to a date.

TO_DECIMAL , TO_NUMBER , TO_NUMERIC

Converts an input expression to a fixed-point number.

Conversion Functions

TO_DOUBLE

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

Conversion Functions

TO_GEOGRAPHY

Parses an input and returns a value of type GEOGRAPHY.

TO_GEOMETRY

Parses an input and returns a value of type GEOMETRY.

TO_JSON

Converts a VARIANT value to a string containing the JSON representation of the value.

TO_OBJECT

Converts the input value to an OBJECT.

TO_TIME , TIME

Converts an input expression into a time.

TO_TIMESTAMP / TO_TIMESTAMP_*

Converts an input expression into the corresponding timestamp.

TO_VARIANT

Converts any value to a VARIANT value or NULL (if input is NULL).

Conversion Functions

TO_XML

Converts a VARIANT to a VARCHAR that contains an XML representation of the value.

TRANSLATE

The TRANSLATE() function replaces characters in a string.

String & Binary Functions

TRIM

Removes leading and trailing characters from a string.

String & Binary Functions

TRUNCATE , TRUNC

Rounds the input expression down to the nearest (or equal) integer closer to zero, or to the nearest equal or smaller value with the specified number of places after the decimal point.

Numeric Functions

TRUNC

Truncates a date, time, or timestamp to the specified part.

Date & Time Functions

TRY_BASE64_DECODE_BINARY

A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding.

String & Binary Functions

TRY_BASE64_DECODE_STRING

A special version of BASE64_DECODE_STRING that returns a NULL value if an error occurs during decoding.

String & Binary Functions

TRY_CAST

A special version of CAST , :: that is available for a subset of data type conversions.

Conversion Functions

TRY_HEX_DECODE_BINARY

A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.

String & Binary Functions

TRY_HEX_DECODE_STRING

A special version of HEX_DECODE_STRING that returns a NULL value if an error occurs during decoding.

String & Binary Functions

TRY_PARSE_JSON

A special version of PARSE_JSON that returns a NULL value if an error occurs during parsing.

Semi-structured Data Functions

TRY_TO_BINARY

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

Conversion Functions

TRY_TO_BOOLEAN

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

Conversion Functions

TRY_TO_DATE

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

TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC

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

Conversion Functions

TRY_TO_DOUBLE

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

Conversion Functions

TRY_TO_GEOGRAPHY

Parses an input and returns a value of type GEOGRAPHY.

TRY_TO_GEOMETRY

Parses an input and returns a value of type GEOMETRY.

TRY_TO_TIME

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

Conversion Functions

TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*

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

Conversion Functions

TYPEOF

Reports the type of a value stored in a VARIANT column.

Semi-structured Data Functions

U

UNICODE

Returns the Unicode code point for the first Unicode character in a string.

String & Binary Functions

UNIFORM

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

Data Generation Functions

UPPER

Returns the input string expr with all characters converted to uppercase.

String & Binary Functions

UUID_STRING

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

V

VALIDATE

Validates the files loaded in a past execution of the COPY INTO <table> command and returns all the errors encountered during the load, rather than just the first error.

Table Functions

This table function can be used to validate data files processed by Snowpipe within a specified time range.

VAR_POP

Returns the population variance of non-NULL records in a group.

VAR_SAMP

Returns the sample variance of non-NULL records in a group.

VARIANCE , VARIANCE_SAMP

Returns the sample variance of non-NULL records in a group.

VARIANCE_POP

Returns the population variance of non-NULL records in a group.

W

This table function can be used to query the activity history (defined as the “query load”) for a single warehouse within a specified date range.

WAREHOUSE_METERING_HISTORY

This table function can be used in queries to return the hourly credit usage for a single warehouse (or all the warehouses in your account) within a specified date range.

WIDTH_BUCKET

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.

Numeric Functions

X

XMLGET

Extracts an XML element object (often referred to as simply a tag) from the content of the outer XML element based on the name and instance number of the specified tag.

Semi-structured Data Functions

Y

YEAR* / DAY* / WEEK* / MONTH / QUARTER

Extracts the corresponding date part from a date or timestamp.

Date & Time Functions

Z

ZEROIFNULL

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

Conditional Expression Functions

ZIPF

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

Data Generation Functions