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

ADD_MONTHS

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

Date & time functions

ALERT_HISTORY

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

Information Schema , Table 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.

Aggregate functions , Window functions

APPLICATION_JSON

Returns a JSON object that specifies the JSON message to use for a notification.

Notification functions

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

Aggregate functions , Window functions

APPROX_PERCENTILE

Returns an approximated value for the desired percentile (that is, if column c has n numbers, APPROX_PERCENTILE(c, p) returns a number such that approximately n * p of the numbers in c are smaller than the returned number).

Aggregate functions , Window functions

APPROX_PERCENTILE_ACCUMULATE

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

Aggregate functions , Window function syntax and usage

APPROX_PERCENTILE_COMBINE

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

Aggregate functions , Window function syntax and usage

APPROX_PERCENTILE_ESTIMATE

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

Aggregate functions , Window function syntax and usage

APPROX_TOP_K

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

Aggregate functions , Window functions

APPROX_TOP_K_ACCUMULATE

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

Aggregate functions , Window function syntax and usage

APPROX_TOP_K_COMBINE

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

Aggregate functions , Window function syntax and usage

APPROX_TOP_K_ESTIMATE

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

Aggregate functions , Window function syntax and usage

APPROXIMATE_JACCARD_INDEX

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

Aggregate functions , Window functions

APPROXIMATE_SIMILARITY

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

Aggregate functions , Window function syntax and usage

ARRAY_AGG

Returns the input values, pivoted into an array.

Aggregate functions , Window functions , Semi-structured and structured data functions

ARRAY_APPEND

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

Semi-structured and structured data functions

ARRAY_CAT

Returns a concatenation of two arrays.

Semi-structured and structured data functions

ARRAY_COMPACT

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

Semi-structured and structured data functions

ARRAY_CONSTRUCT

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

Semi-structured and 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 and structured data functions

ARRAY_CONTAINS

Returns TRUE if the specified value is found in the specified array.

Semi-structured and structured data functions

ARRAY_DISTINCT

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

Semi-structured and 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 and structured data functions

ARRAY_FLATTEN

Flattens an ARRAY of ARRAYs into a single ARRAY.

Semi-structured and structured data functions

ARRAY_GENERATE_RANGE

Returns an ARRAY of integer values within a specified range (e.g. [2, 3, 4]).

Semi-structured and structured data functions

ARRAY_INSERT

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

Semi-structured and structured data functions

ARRAY_INTERSECTION

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

Semi-structured and structured data functions

ARRAY_MAX

Given an input ARRAY, returns the element with the highest value that is not a SQL NULL.

Semi-structured and structured data functions

ARRAY_MIN

Given an input ARRAY, returns the element with the lowest value that is not a SQL NULL.

Semi-structured and structured data functions

ARRAY_POSITION

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

Semi-structured and structured data functions

ARRAY_PREPEND

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

Semi-structured and structured data functions

ARRAY_REMOVE

Given a source ARRAY, returns an ARRAY with elements of the specified value removed.

Semi-structured and structured data functions

ARRAY_REMOVE_AT

Given a source ARRAY, returns an ARRAY with the element at the specified position removed.

Semi-structured and structured data functions

ARRAY_REVERSE

Returns an array with the elements of the input array in reverse order.

Semi-structured and structured data functions

ARRAY_SIZE

Returns the size of the input array.

Semi-structured and structured data functions

ARRAY_SLICE

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

Semi-structured and structured data functions

ARRAY_SORT

Returns an ARRAY that contains the elements of the input ARRAY sorted in ascending or descending order.

Semi-structured and 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 and structured data functions

ARRAY_UNION_AGG

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

Aggregate functions , Window functions

ARRAY_UNIQUE_AGG

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

Aggregate functions , Window functions

ARRAYS_OVERLAP

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

Semi-structured and structured data functions

ARRAYS_TO_OBJECT

Returns an OBJECT that contains the keys specified by one input ARRAY and the values specified by another input ARRAY.

Semi-structured and structured data functions

ARRAYS_ZIP

Returns an array of objects, each of which contains key-value pairs for an nth element in the input arrays.

Semi-structured and 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 and structured data functions

AS_ARRAY

Casts a VARIANT value to an array.

Semi-structured and structured data functions

AS_BINARY

Casts a VARIANT value to a binary string.

Semi-structured and structured data functions

AS_BOOLEAN

Casts a VARIANT value to a BOOLEAN value.

Semi-structured and structured data functions

AS_CHAR , AS_VARCHAR

Casts a VARIANT value to a string.

Semi-structured and structured data functions

AS_DATE

Casts a VARIANT value to a date.

Semi-structured and 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 and structured data functions

AS_DOUBLE , AS_REAL

Casts a VARIANT value to a floating-point value.

Semi-structured and structured data functions

AS_INTEGER

Casts a VARIANT value to an integer.

Semi-structured and structured data functions

AS_OBJECT

Casts a VARIANT value to an object.

Semi-structured and structured data functions

AS_TIME

Casts a VARIANT value to a time value.

Semi-structured and structured data functions

AS_TIMESTAMP_*

Casts a VARIANT value to the respective TIMESTAMP value.

Semi-structured and 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.

Information Schema , Table functions

AUTOMATIC_CLUSTERING_HISTORY

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

Information Schema , Table functions

AVG

Returns the average of non-NULL records.

Aggregate functions , Window functions

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 or binary expressions.

Bitwise expression functions

BITAND_AGG

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

Aggregate functions , Window functions , Bitwise expression functions

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 or binary expression.

Bitwise expression functions

BITOR

Returns the bitwise OR of two numeric or binary expressions.

Bitwise expression functions

BITOR_AGG

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

Aggregate functions , Window functions , Bitwise expression functions

BITSHIFTLEFT

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

Bitwise expression functions

BITSHIFTRIGHT

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

Bitwise expression functions

BITXOR

Returns the bitwise XOR of two numeric or binary expressions.

Bitwise expression functions

BITXOR_AGG

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

Aggregate functions , Window functions , Bitwise expression functions

BOOLAND

Computes the Boolean AND of two numeric expressions.

Conditional expression functions

BOOLAND_AGG

Returns TRUE if all non-NULL Boolean records in a group evaluate to TRUE.

Aggregate functions , Window functions , Conditional expression functions

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 TRUE if at least one Boolean record in a group evaluates to TRUE.

Aggregate functions , Window functions , Conditional expression functions

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 TRUE if exactly one Boolean record in the group evaluates to TRUE.

Aggregate functions , Window functions , Conditional expression functions

BUILD_SCOPED_FILE_URL

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

File functions

BUILD_STAGE_FILE_URL

Generates a Snowflake 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 and structured data functions

CHECK_XML

Checks the validity of an XML document.

Semi-structured and 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

CLASSIFY_TEXT (SNOWFLAKE.CORTEX)

Classifies free-form text data into categories you provide.

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

COMPLETE (SNOWFLAKE.CORTEX)

Given a prompt, generates a response (completion) using your choice of supported language model.

String & binary functions

COMPLETE_TASK_GRAPHS

Returns the status of a completed graph run.

Information Schema , Table functions

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.

Information Schema , Table functions

CORR

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

Aggregate functions , Window functions

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.

Aggregate functions , Window functions

COUNT_IF

Returns the number of records that satisfy a condition or NULL if no records satisfy the condition.

Aggregate functions , Window functions

COUNT_TOKENS (SNOWFLAKE.CORTEX)

Returns the number of tokens in a prompt for the large language model or the task-specific function specified in the argument.

String & binary functions

COVAR_POP

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

Aggregate functions , Window functions

COVAR_SAMP

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

Aggregate functions , Window functions

CUME_DIST

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

Window functions

CUMULATIVE_PRIVACY_LOSSES

Returns the privacy budgets associated with a specific privacy policy.

Table functions

CURRENT_ACCOUNT

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

Context functions

CURRENT_ACCOUNT_NAME

Returns the name of the current account.

Context functions

CURRENT_AVAILABLE_ROLES

Returns a list of all account-level 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 current database, which varies depending on where you call the function.

Context functions

CURRENT_DATE

Returns the current date of the system.

Context functions

CURRENT_IP_ADDRESS

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

Context functions

CURRENT_ORGANIZATION_NAME

Returns the name of the organization to which the current account belongs.

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 when the primary role is an account-level role or NULL if the role in use for the current session is a database role.

Context functions

CURRENT_ROLE_TYPE

Returns either ROLE or DATABASE_ROLE based on whether the current active role in the session is an account role or a database role, respectively.

Context functions

CURRENT_SCHEMA

Returns the name of the current schema, which varies depending on where you call the function.

Context functions

CURRENT_SCHEMAS

Returns active search path schemas.

Context functions

CURRENT_SECONDARY_ROLES

Returns 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

CURRENT_TASK_GRAPHS

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

Information Schema , Table functions

CURRENT_TIME

Returns the current time for the system.

Context functions

CURRENT_TIMESTAMP

Returns the current timestamp for the system in the local time zone.

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_METRIC_FUNCTION_REFERENCES

Returns a row for each object that has the specified data metric function assigned to the object or returns a row for each data metric function assigned to the specified object.

Information Schema , Table functions

DATA_QUALITY_MONITORING_RESULTS

Returns a row for each data metric function assigned to the specified object, which includes the evaluation result and other metadata of the data metric function on the object.

LOCAL , Table functions

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.

Information Schema , Table functions

DATABASE_REFRESH_HISTORY

Returns the refresh history for a secondary database.

Information Schema , Table functions

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.

Information Schema , Table functions

DATABASE_REPLICATION_USAGE_HISTORY

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

Information Schema , Table functions

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.

Information Schema , Table functions

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 value to the specified precision.

Date & time functions

DATEADD

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

Converts radians to degrees.

Numeric functions

DENSE_RANK

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

Window function syntax and usage

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

AVG (system data metric function)

Returns the average value for the specified column in a table.

Data metric functions

BLANK_COUNT (system data metric function)

Returns the count of column values that are blank for the specified column in a table.

Data metric functions

BLANK_PERCENT (system data metric function)

Returns the percentage of column values that are blank for the specified column in a table.

Data metric functions

DATA_METRIC_SCHEDULED_TIME (system data metric function)

Returns the timestamp for when a DMF is scheduled to run or the current timestamp if the function is called manually.

Data metric functions

DUPLICATE_COUNT (system data metric function)

Returns the count of column values that have duplicates, including NULL values.

Data metric functions

FRESHNESS (system data metric function)

Returns the difference in seconds between the maximum value of a timestamp column and the scheduled time when the data metric function runs.

Data metric functions

MAX (system data metric function)

Returns the maximum value for the specified column in a table.

Data metric functions

MIN (system data metric function)

Returns the minimum value for the specified column in a table.

Data metric functions

NULL_COUNT (system data metric function)

Returns the total number of NULL values for the specified columns in a table.

Data metric functions

NULL_PERCENT (system data metric function)

Returns the percentage of columns values that are NULL for the specified column in a table.

Data metric functions

ROW_COUNT (system data metric function)

Returns the total number of rows for the specified table.

Data metric functions

STDDEV (system data metric function)

Returns the standard deviation value for the specified column in a table.

Data metric functions

UNIQUE_COUNT (system data metric function)

Returns the total number of unique non-NULL values for the specified columns in a table.

Data metric functions

DP_INTERVAL_HIGH

Returns the upper bound of the noise interval, which is used by differential privacy to help analysts determine how much noise has been introduced into query results.

Differential privacy functions

DP_INTERVAL_LOW

Returns the lower bound of the noise interval, which is used by differential privacy to help analysts determine how much noise has been introduced into query results.

Differential privacy functions

DYNAMIC_TABLE_GRAPH_HISTORY

This table function returns information on all dynamic tables in the current account.

Information Schema , Table functions

DYNAMIC_TABLE_REFRESH_HISTORY

This table function returns information about each refresh (completed and running) of dynamic tables.

Information Schema , Table functions

DYNAMIC_TABLES

This table function returns metadata about dynamic tables, including aggregate lag metrics and the status of the most recent refreshes, within 7 days of the current time.

Information Schema , Table functions

E

EDITDISTANCE

Computes the Levenshtein distance between two input strings.

String & binary functions

EMAIL_INTEGRATION_CONFIG

Returns a JSON object that specifies the email notification integration, recipients, and subject line to use for an email notification.

Notification functions

EMBED_TEXT_768 (SNOWFLAKE.CORTEX)

Creates a vector embedding of 768 dimensions from English-language text.

String & binary functions

EMBED_TEXT_1024 (SNOWFLAKE.CORTEX)

Creates a vector embedding of 1024 dimensions from text.

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 the second expression.

String & binary functions

EQUAL_NULL

Compares whether two expressions are equal.

Conditional expression functions

ESTIMATE_REMAINING_DP_AGGREGATES

Returns the estimated number of aggregation functions that can be run before the limit of a privacy budget is reached.

Differential privacy functions , Table 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.

Information Schema , Table functions

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.

Information Schema , Table functions

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

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

Information Schema , Table functions

EXTRACT

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

Date & time functions

EXTRACT_ANSWER (SNOWFLAKE.CORTEX)

Extracts an answer to a given question from a text document.

String & binary functions

EXTRACT_SEMANTIC_CATEGORIES

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

System functions

F

FACTORIAL

Computes the factorial of its input.

Numeric functions

FILTER

Filters an array based on the logic in a lambda expression.

Semi-structured and structured data functions

FINETUNE ('CANCEL') (SNOWFLAKE.CORTEX)

Cancels the specified fine-tuning job from the current schema.

FINETUNE ('CREATE') (SNOWFLAKE.CORTEX)

Creates a fine-tuning job.

FINETUNE ('DESCRIBE') (SNOWFLAKE.CORTEX)

Describes the properties of a fine-tuning job.

FINETUNE ('SHOW') (SNOWFLAKE.CORTEX)

Lists the fine-tuning jobs for which you have access privileges.

FINETUNE (SNOWFLAKE.CORTEX)

This function lets you create and manage large language models customized for your specific task.

String & binary functions

FIRST_VALUE

Returns the first value within an ordered group of values.

Window function syntax and usage

FLATTEN

Flattens (explodes) compound values into multiple rows.

Table functions , Semi-structured and structured data functions

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.

Metadata functions

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 and 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_ANACONDA_PACKAGES_REPODATA

Returns a list of third-party packages that are available from Anaconda.

System functions

GET_CONDITION_QUERY_UUID

Returns the query ID for the SQL statement executed for the condition of an alert.

Context functions

GET_DDL

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

Metadata functions

GET_IGNORE_CASE

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

Semi-structured and 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 and structured data functions

GET_PRESIGNED_URL

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

File functions

GET_QUERY_OPERATOR_STATS

Returns statistics about individual query operators within a query that has completed.

System functions , Table 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

GETDATE

Returns the current timestamp for the system in the local time zone.

Context functions

GETVARIABLE

Returns the value associated with a SQL variable name.

Context functions

GREATEST

Returns the largest value from a list of expressions.

Conditional expression functions

GREATEST_IGNORE_NULLS

Returns the largest non-NULL 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

H3_CELL_TO_BOUNDARY

Returns the GEOGRAPHY object representing the boundary of an H3 cell.

Geospatial functions

H3_CELL_TO_CHILDREN

Returns an array of the INTEGER IDs of the children of an H3 cell for a given resolution.

Geospatial functions

H3_CELL_TO_CHILDREN_STRING

Returns an array of the VARCHAR values containing the hexadecimal IDs of the children of an H3 cell for a given resolution.

Geospatial functions

H3_CELL_TO_PARENT

Returns the ID of the parent of an H3 cell for a given resolution.

Geospatial functions

H3_CELL_TO_POINT

Returns the GEOGRAPHY object representing the Point that is the centroid of an H3 cell.

Geospatial functions

H3_COMPACT_CELLS

Returns an array of VARIANT values that contain the INTEGER IDs of fewer, larger H3 cells that cover the same area as the H3 cells in the input.

Geospatial functions

H3_COMPACT_CELLS_STRINGS

Returns an array of VARIANT values that contain the VARCHAR hexadecimal IDs of fewer, larger H3 cells that cover the same area as the H3 cells in the input.

Geospatial functions

H3_COVERAGE

Returns an array of IDs (as INTEGER values) identifying the minimal set of H3 cells that completely cover a shape (specified by a GEOGRAPHY object).

Geospatial functions

H3_COVERAGE_STRINGS

Returns an array of hexadecimal IDs (as VARCHAR values) identifying the minimal set of H3 cells that completely cover a shape (specified by a GEOGRAPHY object).

Geospatial functions

H3_GET_RESOLUTION

Returns the resolution of an H3 cell.

Geospatial functions

H3_GRID_DISK

Returns an array of the IDs of the H3 cells that are within the k-distance from the specified cell.

Geospatial functions

H3_GRID_DISTANCE

Returns the distance between two H3 cells specified by their IDs.

Geospatial functions

H3_GRID_PATH

Returns an array of the IDs of the H3 cells that represent the line between two cells.

Geospatial functions

H3_INT_TO_STRING

Converts the INTEGER value of an H3 cell ID to hexadecimal format.

Geospatial functions

H3_IS_PENTAGON

Returns TRUE if the boundary of an H3 cell represents a pentagon.

Geospatial functions

H3_IS_VALID_CELL

Returns TRUE if the input represents a valid H3 cell.

Geospatial functions

H3_LATLNG_TO_CELL

Returns the INTEGER value of the H3 cell ID for a given latitude, longitude, and resolution.

Geospatial functions

H3_LATLNG_TO_CELL_STRING

Returns the H3 cell ID in hexadecimal format (as a VARCHAR value) for a given latitude, longitude, and resolution.

Geospatial functions

H3_POINT_TO_CELL

Returns the INTEGER value of an H3 cell ID for a Point (specified by a GEOGRAPHY object) at a given resolution.

Geospatial functions

H3_POINT_TO_CELL_STRING

Returns the hexadecimal value of an H3 cell ID for a Point (specified by a GEOGRAPHY object) at a given resolution.

Geospatial functions

H3_POLYGON_TO_CELLS

Returns an array of INTEGER values of the IDs of H3 cells that have centroids contained by a Polygon (specified by a GEOGRAPHY object).

Geospatial functions

H3_POLYGON_TO_CELLS_STRINGS

Returns an array of VARCHAR values of the hexadecimal IDs of H3 cells that have centroids contained by a Polygon (specified by a GEOGRAPHY object).

Geospatial functions

H3_STRING_TO_INT

Converts an H3 cell ID in hexadecimal format to an INTEGER value.

Geospatial functions

H3_TRY_COVERAGE

A special version of H3_COVERAGE that returns NULL if an error occurs when it attempts to return an array of IDs (as INTEGER values) identifying the minimal set of H3 cells that completely cover a shape (specified by a GEOGRAPHY object).

Geospatial functions

H3_TRY_COVERAGE_STRINGS

A special version of H3_COVERAGE_STRINGS that returns NULL if an error occurs when it attempts to return an array of hexadecimal IDs (as VARCHAR values) identifying the minimal set of H3 cells that completely cover a shape (specified by a GEOGRAPHY object).

Geospatial functions

H3_TRY_GRID_DISTANCE

A special version of H3_GRID_DISTANCE that returns NULL if an error occurs when it attempts to return the distance between two H3 cells.

Geospatial functions

H3_TRY_GRID_PATH

A special version of H3_GRID_PATH that returns NULL if an error occurs when it attempts to return an array of VARIANT values that contain the IDs of the H3 cells that represent the line between two cells.

Geospatial functions

H3_TRY_POLYGON_TO_CELLS

A special version of H3_POLYGON_TO_CELLS that returns NULL if an error occurs when it attempts to return an array of INTEGER values of the IDs of H3 cells that have centroids contained by a Polygon (specified by a GEOGRAPHY object).

Geospatial functions

H3_TRY_POLYGON_TO_CELLS_STRINGS

A special version of H3_POLYGON_TO_CELLS_STRINGS that returns NULL if an error occurs when it attempts to return an array of VARCHAR values of the hexadecimal IDs of H3 cells that have centroids contained by a Polygon (specified by a GEOGRAPHY object).

Geospatial functions

H3_UNCOMPACT_CELLS

Returns an array of VARIANT values that contain the INTEGER IDs of H3 cells at the specified resolution that cover the same area as the H3 cells in the input.

Geospatial functions

H3_UNCOMPACT_CELLS_STRINGS

Returns an array of VARIANT values that contain the VARCHAR hexadecimal IDs of H3 cells at the specified resolution that cover the same area as the H3 cells in the input.

Geospatial functions

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.

Aggregate functions , Window functions

HAVERSINE

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

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

Aggregate functions , Window functions

HLL_ACCUMULATE

Returns the HyperLogLog state at the end of aggregation.

Aggregate functions , Window function syntax and usage

HLL_COMBINE

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

Aggregate functions , Window function syntax and usage

HLL_ESTIMATE

Returns the cardinality estimate for the given HyperLogLog state.

Aggregate functions , Window function syntax and usage

HLL_EXPORT

Converts input in BINARY format to OBJECT format.

Aggregate functions , Window function syntax and usage

HLL_IMPORT

Converts input in OBJECT format to BINARY format.

Aggregate functions , Window function syntax and usage

HOUR / MINUTE / SECOND

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

Date & time functions

I

IFF

Returns one of two values depending on whether a Boolean expression evaluates to true or false.

Conditional expression functions

IFNULL

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

Conditional expression functions

[ NOT ] ILIKE

Performs a case-insensitive comparison to determine whether a string matches or does not match a specified pattern.

String & binary functions

ILIKE ANY

Performs a case-insensitive comparison to match a string against any of one or more specified 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

INTEGRATION

Returns a JSON object that specifies the notification integration to use to send a message.

Notification functions

INVOKER_ROLE

Returns the name of the account-level role of the object executing the query or NULL if the name of the role is a database role.

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 and structured data functions

IS_APPLICATION_ROLE_IN_SESSION

Verifies whether the application role is activated in the consumer’s current session.

Context functions

IS_ARRAY

Returns TRUE if its VARIANT argument contains an ARRAY value.

Semi-structured and structured data functions

IS_BINARY

Returns TRUE if its VARIANT argument contains a binary string.

Semi-structured and structured data functions

IS_BOOLEAN

Returns TRUE if its VARIANT argument contains a Boolean value.

Semi-structured and structured data functions

IS_CHAR , IS_VARCHAR

Returns TRUE if its VARIANT argument contains a string value.

Semi-structured and structured data functions

IS_DATABASE_ROLE_IN_SESSION

Verifies whether the database role is in the user’s active primary or secondary role hierarchy for the current session or if the specified column contains a database role that is in the user’s active primary or secondary role hierarchy for the current session.

Context functions

IS_DATE , IS_DATE_VALUE

Verifies whether a VARIANT value contains a DATE value.

Semi-structured and structured data functions

IS_DECIMAL

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

Semi-structured and 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 and 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 based on the context in which the function is called.

Context functions

IS_INSTANCE_ROLE_IN_SESSION

Verifies whether the user’s active primary or secondary role hierarchy for the session inherits the specified instance role.

Context functions

IS_INTEGER

Returns TRUE if its VARIANT argument contains an integer value.

Semi-structured and structured data functions

IS_NULL_VALUE

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

Conditional expression functions , Semi-structured and structured data functions

IS_OBJECT

Returns TRUE if its VARIANT argument contains an OBJECT value.

Semi-structured and structured data functions

IS_ROLE_IN_SESSION

Verifies whether the account 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 and structured data functions

IS_TIMESTAMP_*

Verifies whether a VARIANT value contains the respective TIMESTAMP value.

Semi-structured and 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 and structured data functions

K

KURTOSIS

Returns the population excess kurtosis of non-NULL records.

Aggregate functions , Window function syntax and usage

L

LAG

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

Window function syntax and usage

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_SUCCESSFUL_SCHEDULED_TIME

Returns the timestamp representing the scheduled time for the most recent successful evaluation of the alert condition, where no errors occurred when executing the action.

Date & time 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 function syntax and usage

LEAD

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

Window function syntax and usage

LEAST

Returns the smallest value from a list of expressions.

Conditional expression functions

LEAST_IGNORE_NULLS

Returns the smallest non-NULL 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

[ NOT ] LIKE

Performs a case-sensitive comparison to determine whether a string matches or does not match a specified pattern.

String & binary functions

LIKE ALL

Performs a case-sensitive comparison to match a string against all of one or more specified patterns.

String & binary functions

LIKE ANY

Performs a case-sensitive comparison to match a string against any of one or more specified patterns.

String & binary functions

LISTAGG

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

Aggregate functions , Window function syntax and usage

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 in the local time zone.

Context functions

LOG

Returns the logarithm of a numeric expression.

Numeric functions

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

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

Information Schema , Table functions

LOWER

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

String & binary functions

LPAD

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

MAP_CAT

Returns the concatenatation of two MAPs.

Semi-structured and structured data functions

MAP_CONTAINS_KEY

Determines whether the specified MAP contains the specified key.

Semi-structured and structured data functions

MAP_DELETE

Returns a MAP based on an existing MAP with one or more keys removed..

Semi-structured and structured data functions

MAP_INSERT

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

Semi-structured and structured data functions

MAP_KEYS

Returns the keys in a MAP.

Semi-structured and structured data functions

MAP_PICK

Returns a new MAP containing the specified key-value pairs from an existing MAP.

Semi-structured and structured data functions

MAP_SIZE

Returns the size of a MAP.

Semi-structured and structured data functions

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.

Information Schema , Table functions

MAX

Returns the maximum value for the records within expr.

Aggregate functions , Window functions

MAX_BY

Finds the row(s) containing the maximum value for a column and returns the value of another column in that row.

Aggregate functions

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.

Aggregate functions , Window functions

MIN

Returns the minimum value for the records within expr.

Aggregate functions , Window functions

MIN_BY

Finds the row(s) containing the minimum value for a column and returns the value of another column in that row.

Aggregate functions

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.

Aggregate functions , Window function syntax and usage

MINHASH_COMBINE

Combines input MinHash states into a single MinHash output state.

Aggregate functions , Window function syntax and usage

MOD

Returns the remainder of input expr1 divided by input expr2.

Numeric functions

MODE

Returns the most frequent value for the values within expr1.

Aggregate functions , Window functions

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

NETWORK_RULE_REFERENCES

Returns a row for each object with which the specified network rule is associated or returns a row for each network rule associated with the specified container.

Information Schema , Table functions

NEXT_DAY

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

Date & time functions

NORMAL

Generates a normally-distributed pseudo-random floating point number with specified mean and stddev (standard deviation).

Data generation functions

NOTIFICATION_HISTORY

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

Information Schema , Table functions

NTH_VALUE

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

Window function syntax and usage

NTILE

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

Window function syntax and usage

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.

Aggregate functions , Window functions , Semi-structured and structured data functions

OBJECT_CONSTRUCT

Returns an OBJECT constructed from the arguments.

Semi-structured and structured data functions

OBJECT_CONSTRUCT_KEEP_NULL

Returns an OBJECT constructed from the arguments that retains key-values pairs with NULL values.

Semi-structured and 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 and 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 and structured data functions

OBJECT_KEYS

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

Semi-structured and structured data functions

OBJECT_PICK

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

Semi-structured and structured data functions

OCTET_LENGTH

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

String & binary functions

P

PARSE_DOCUMENT (SNOWFLAKE.CORTEX)

Returns the extracted content from a document on a Snowflake stage as an OBJECT that contains JSON-encoded objects as strings.

String & binary functions

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

Aggregate functions , Window functions

PERCENTILE_DISC

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

Aggregate functions , Window functions

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.

Information Schema , Table functions

POLICY_CONTEXT

Simulates the results of a query based upon the value of one or more context functions, which lets you determine how policies affect query results.

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.

Information Schema , Table functions

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.

Information Schema , Table functions

QUERY_HISTORY , QUERY_HISTORY_BY_*

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

Information Schema , Table functions

R

RADIANS

Converts degrees to radians.

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

REDUCE

Reduces an array to a single value based on the logic in a lambda expression.

Semi-structured and structured data functions

[ NOT ] REGEXP

Performs a comparison to determine whether a string matches or does not match a 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

Performs a comparison to determine whether a string matches a 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.

Aggregate functions , Window functions

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.

Aggregate functions , Window functions

REGR_COUNT

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

Aggregate functions , Window function syntax and usage

REGR_INTERCEPT

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

Aggregate functions , Window function syntax and usage

REGR_R2

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

Aggregate functions , Window function syntax and usage

REGR_SLOPE

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

Aggregate functions , Window function syntax and usage

REGR_SXX

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

Aggregate functions , Window function syntax and usage

REGR_SXY

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

Aggregate functions , Window function syntax and usage

REGR_SYY

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

Aggregate functions , Window function syntax and usage

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

String & binary functions

REPLICATION_GROUP_REFRESH_HISTORY

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

Information Schema , Table functions

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.

Information Schema , Table functions

REPLICATION_GROUP_USAGE_HISTORY

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

Information Schema , Table functions

REPLICATION_USAGE_HISTORY

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

Information Schema , Table functions

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

[ NOT ] RLIKE

Performs a comparison to determine whether a string matches or does not match a 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 function syntax and usage

RPAD

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

SANITIZE_WEBHOOK_CONTENT

Removes placeholders (for example, the SNOWFLAKE_WEBHOOK_SECRET placeholder, which specifies a secret) from the body of a notification message to be sent.

Notification functions

SCHEDULED_TIME

Returns the timestamp representing the scheduled time of the current alert.

Date & time functions

SEARCH

Searches character data (text) in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns.

String & binary functions

SEARCH_IP

Searches for valid IPv4 addresses in specified character-string columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns.

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.

Information Schema , Table functions

SEARCH_PREVIEW (SNOWFLAKE.CORTEX)

Given a Cortex Search service name, and a query, returns a response from the specified service.

String & binary functions

SENTIMENT (SNOWFLAKE.CORTEX)

Returns a sentiment score for the given English-language input text.

String & binary functions

SEQ1 / SEQ2 / SEQ4 / SEQ8

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

Data generation functions

SERVERLESS_ALERT_HISTORY

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

Information Schema , Table functions

SERVERLESS_TASK_HISTORY

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

Information Schema , Table functions

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

SHOW_PYTHON_PACKAGES_DEPENDENCIES

Returns a list of the dependencies and their versions for the Python packages that were specified.

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

String & binary functions , Table functions

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 a Point that represents the origin (the location of the observer) and a specified Point, returns the azimuth in radians.

Geospatial functions

ST_BUFFER

Returns a GEOMETRY object that represents a MultiPolygon containing the points within a specified distance of the input GEOMETRY object.

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

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

Geospatial functions

ST_COVERS

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.

Geospatial functions , Conversion functions

ST_GEOGPOINTFROMGEOHASH

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

Geospatial functions , Conversion functions

ST_GEOGRAPHYFROMWKB

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

Geospatial functions , Conversion functions

ST_GEOGRAPHYFROMWKT

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

Geospatial functions , Conversion functions

ST_GEOHASH

Returns the geohash for a GEOGRAPHY or GEOMETRY 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.

Geospatial functions , Conversion functions

ST_GEOMETRYFROMWKT

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

Geospatial functions , Conversion functions

ST_GEOMFROMGEOHASH

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

Geospatial functions

ST_GEOMPOINTFROMGEOHASH

Returns a GEOMETRY object for the point that represents center of a geohash.

Geospatial functions

ST_HAUSDORFFDISTANCE

Returns the discrete Hausdorff distance between two GEOGRAPHY objects.

Geospatial functions

ST_INTERPOLATE

Given an input GEOGRAPHY object, returns an interpolated object that is within a specified tolerance.

Geospatial functions

ST_INTERSECTION

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

Geospatial functions

ST_INTERSECTION_AGG

Given a GEOGRAPHY column, returns a GEOGRAPHY object that represents the shape containing the combined set of points that are common to the shapes represented by the objects in the column (that is, the intersection of the shapes).

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_ISVALID

Returns TRUE if the specified GEOGRAPHY or GEOMETRY object represents a valid shape.

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_GEOMPOINT

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 GEOMETRY object that represents a Polygon without holes.

Geospatial functions

ST_MAKEPOLYGONORIENTED

Constructs a 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 or GEOMETRY 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_TRANSFORM

Converts a GEOMETRY object from one spatial reference system (SRS) to another.

Geospatial functions

ST_UNION

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

Geospatial functions

ST_UNION_AGG

Given a GEOGRAPHY column, returns a GEOGRAPHY object that represents the combined set of points that are in at least one of the shapes represented by the objects in the column (that is, the union of the shapes).

Geospatial functions

ST_WITHIN

Returns true if 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.

Information Schema , Table functions

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.

Information Schema , Table functions

STARTSWITH

Returns true if expr1 starts with expr2.

String & binary functions

STDDEV, STDDEV_SAMP

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

Aggregate functions , Window function syntax and usage

STDDEV_POP

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

Aggregate functions , Window function syntax and usage

STRIP_NULL_VALUE

Converts a JSON null value to a SQL NULL value.

Semi-structured and 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.

String & binary functions , Table functions

STRTOK_TO_ARRAY

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

String & binary functions , Semi-structured and structured data functions

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.

Aggregate functions , Window function syntax and usage

SUMMARIZE (SNOWFLAKE.CORTEX)

Summarizes the given English-language input text.

String & binary functions

SYSDATE

Returns the current timestamp for the system 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$ADD_EVENT (for Snowflake Scripting)

Add an event for trace.

System functions

SYSTEM$ALLOWLIST

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

SYSTEM$ALLOWLIST_PRIVATELINK

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

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$AUTO_REFRESH_STATUS

Returns the automated refresh status for an externally managed Iceberg table.

SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS

Returns the status of the specified behavior change release bundle for the current account.

System functions

SYSTEM$BLOCK_INTERNAL_STAGES_PUBLIC_ACCESS

Prevents all public traffic from accessing the internal stage of the current Snowflake account on Microsoft Azure.

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$CLEANUP_DATABASE_ROLE_GRANTS

Revokes privileges on dropped objects from the share and grants the database role to the share.

System functions

SYSTEM$CLIENT_VERSION_INFO

Returns version information for Snowflake clients and drivers.

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$COMMIT_MOVE_ORGANIZATION_ACCOUNT

Finalizes the process of moving an organization account from one region to another.

System functions

SYSTEM$CONVERT_PIPES_SQS_TO_SNS

Convert pipes using Amazon SQS (Simple Queue Service) notifications to the Amazon Simple Notification Service (SNS) service for an S3 bucket.

System functions

SYSTEM$CREATE_BILLING_EVENT

Creates a billable event that tracks consumer usage of an installed monetized application.

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$DATA_METRIC_SCAN

Returns the rows identified by a data quality metric as containing data that failed a data quality check.

System functions , Table 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$DEPROVISION_PRIVATELINK_ENDPOINT

Deprovisions a private connectivity endpoint in the Snowflake VPC or VNet to prevent Snowflake from connecting to an external service using private connectivity.

System functions

SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE

Disables the behavior changes included in the specified behavior change 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 behavior change release bundle for the current account.

System functions

SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS

Returns estimated costs associated with enabling Automatic Clustering for a table.

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$FINISH_OAUTH_FLOW

Sets the OAUTH_REFRESH_TOKEN parameter value of the secret passed as an argument in the SYSTEM$START_OAUTH_FLOW call that began the OAuth flow.

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_CLASSIFICATION_RESULT

Returns the classification result of the specified object.

System functions

SYSTEM$GET_CMK_AKV_CONSENT_URL

Returns a consent URL to the Azure Key Vault account related to customer-managed keys.

System functions

SYSTEM$GET_CMK_CONFIG

Returns configuration information for use with customer-managed keys (CMKs) and Tri-Secret Secure.

System functions

SYSTEM$GET_CMK_INFO

Returns a status about your customer-managed key (CMK) for use with Tri-Secret Secure.

System functions

SYSTEM$GET_CMK_KMS_KEY_POLICY

Returns an ARRAY containing a snippet of the AWS Key Management Service policy information related to customer-managed keys.

System functions

SYSTEM$GET_COMPUTE_POOL_STATUS

Retrieves status of a compute pool.

System functions

SYSTEM$GET_DIRECTORY_TABLE_STATUS

Returns a list of records that contain the directory table consistency status for stages in your account.

System functions

SYSTEM$GET_GCP_KMS_CMK_GRANT_ACCESS_CMD

Returns a Google Cloud gcloud command to obtain policy information for the Google Cloud Key Management Service for use with customer-managed keys.

System functions

SYSTEM$GET_ICEBERG_TABLE_INFORMATION

Returns the location of the root metadata file and status of the latest snapshot for an Apache Icebergβ„’ table.

System functions

SYSTEM$GET_LOGIN_FAILURE_DETAILS

Returns a JSON object that represents an unsuccessful login attempt associated with External OAuth, SAML, or key pair authentication.

System functions

SYSTEM$GET_PREDECESSOR_RETURN_VALUE

Retrieves the return value for the predecessor task in a task graph.

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 Snowflake internal stages.

System functions

SYSTEM$GET_PRIVATELINK_ENDPOINTS_INFO

Returns the status of all private connectivity endpoints that you provision.

System functions

SYSTEM$GET_SERVICE_LOGS

Retrieves local logs from a Snowpark Container Services service container.

System functions

SYSTEM$GET_SERVICE_STATUS β€” Deprecated

Retrieves the status of a Snowpark Container Services service.

System functions

SYSTEM$GET_SNOWFLAKE_PLATFORM_INFO

Returns platform information for the cloud provider that hosts your Snowflake account.

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$GET_TASK_GRAPH_CONFIG

Returns the value of the configuration string for the task currently executing when invoked from the statement or stored procedure defined by the task.

System functions

SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER

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

System functions

SYSTEM$INITIATE_MOVE_ORGANIZATION_ACCOUNT

Starts the process of moving an organization account to a new region.

System functions

SYSTEM$INTERNAL_STAGES_PUBLIC_ACCESS_STATUS

Checks to see whether public IP addresses are allowed to access the internal stage of the current Snowflake account on Microsoft Azure.

System functions

SYSTEM$IS_APPLICATION_INSTALLED_FROM_SAME_ACCOUNT

Shows if an app is installed on the same account as the application package it is based on.

System functions

SYSTEM$IS_APPLICATION_SHARING_EVENTS_WITH_PROVIDER

Shows if event sharing is enabled.

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$LIST_APPLICATION_RESTRICTED_FEATURES

Returns a JSON object containing a list of restricted features that the consumer has allowed a

native-app

SYSTEM$LIST_ICEBERG_TABLES_FROM_CATALOG

Lists tables in a remote

iceberg-tm

SYSTEM$LIST_NAMESPACES_FROM_CATALOG

Lists the namespaces in a remote

iceberg-tm

SYSTEM$LOG, SYSTEM$LOG_<level> (for Snowflake Scripting)

Logs a message at the specified severity level.

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_REBINDING_WITH_NOTIFICATION_CHANNEL

Retries the notification channel binding process when a replicated pipe has not been successfully bound to a notification channel during replication time.

System functions

SYSTEM$PIPE_STATUS

Retrieves a JSON representation of the current status of a pipe.

System functions

SYSTEM$PROVISION_PRIVATELINK_ENDPOINT

Provisions a private connectivity endpoint in the Snowflake VPC or VNet to enable Snowflake to connect to an external service using private connectivity.

System functions

SYSTEM$QUERY_REFERENCE

Returns a query reference that you can pass to a stored procedure.

System functions

SYSTEM$REFERENCE

Returns a reference to an object (a table, view, or function).

System functions

SYSTEM$REGISTER_CMK_INFO

Registers your customer-managed key (CMK) for use with Tri-Secret Secure.

System functions

SYSTEM$REGISTRY_LIST_IMAGES β€” Deprecated

Lists images in an image repository.

System functions

SYSTEM$RESTORE_PRIVATELINK_ENDPOINT

Restores a private connectivity endpoint in the Snowflake VPC or VNet to enable Snowflake to connect to an external service using private connectivity.

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$SEND_NOTIFICATIONS_TO_CATALOG

Sends a notification to Snowflake Open Catalog to update Snowflake-managed Apache Icebergβ„’ tables in

opencatalog

SYSTEM$SET_APPLICATION_RESTRICTED_FEATURE_ACCESS

Enables a restricted feature for a

native-app

SYSTEM$SET_EVENT_SHARING_ACCOUNT_FOR_REGION

Sets the event account for a region.

System functions

SYSTEM$SET_RETURN_VALUE

Explicitly sets the return value for a task.

System functions

SYSTEM$SET_SPAN_ATTRIBUTES (for Snowflake Scripting)

Sets attribute name and value associated with a span containing trace events.

System functions

SYSTEM$SHOW_ACTIVE_BEHAVIOR_CHANGE_BUNDLES

Returns an array of the currently available behavior change release bundles, the default state of each bundle, and the actual state of the bundle for the current account.

System functions

SYSTEM$SHOW_BUDGETS_IN_ACCOUNT

Returns the budgets in the account for which you have access privileges.

System functions

SYSTEM$SHOW_EVENT_SHARING_ACCOUNTS

Shows event accounts in a provider organization.

System functions

SYSTEM$SHOW_MOVE_ORGANIZATION_ACCOUNT_STATUS

Returns the status of an attempt to move an organization account.

System functions

SYSTEM$SHOW_OAUTH_CLIENT_SECRETS

Returns the client secrets in a string.

System functions

SYSTEM$SNOWPIPE_STREAMING_UPDATE_CHANNEL_OFFSET_TOKEN

Updates the offset token for a particular channel used by Snowpipe Streaming with a new offset token.

System functions

SYSTEM$START_OAUTH_FLOW

Initiates the OAUTH client flow, returning a URL you use in a browser to complete the OAuth consent process.

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 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 a specified task and all its dependent tasks.

System functions

SYSTEM$TASK_RUNTIME_INFO

Returns information about the current task run.

System functions

SYSTEM$TYPEOF

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

System functions

SYSTEM$UNBLOCK_INTERNAL_STAGES_PUBLIC_ACCESS

Allows traffic from public IP addresses to access the internal stage of the current Snowflake account on Microsoft Azure.

System functions

SYSTEM$UNSET_EVENT_SHARING_ACCOUNT_FOR_REGION

Unsets the events account for a region.

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$VALIDATE_STORAGE_INTEGRATION

Validates the configuration for a specified storage integration.

System functions

SYSTEM$VERIFY_CMK_INFO

Verifies your customer-managed key (CMK) configuration and returns a message about the registered CMK.

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$VERIFY_EXTERNAL_VOLUME

Verifies the configuration for a specified external volume.

System functions

SYSTEM$WAIT

Waits for the specified amount of time before proceeding.

System functions

SYSTEM$WAIT_FOR_SERVICES

Waits for one or more Snowpark Container Services services to reach the READY state (or becomes upgraded) before returning.

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

SYSTEM$WHITELIST_PRIVATELINK β€” Deprecated

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

SYSTIMESTAMP

Returns the current timestamp for the system.

Context functions

T

TAG_REFERENCES

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

Information Schema , Table functions

TAG_REFERENCES_ALL_COLUMNS

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

Information Schema , Table functions

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.

Account Usage table functions , Table functions

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

TASK_DEPENDENTS

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

Information Schema , Table functions

TASK_HISTORY

You can use this table function to query the history of task usage within a specified date range.

Information Schema , Table functions

TEXT_HTML

Returns a JSON object that specifies the HTML message to use for a notification.

Notification functions

TEXT_PLAIN

Returns a JSON object that specifies the plain text message to use for a notification.

Notification functions

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

TIMEADD

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

TIMESTAMPADD

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.

Conversion functions , Semi-structured and structured data functions

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.

Conversion functions , Date & time functions

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.

Geospatial functions , Conversion functions

TO_GEOMETRY

Parses an input and returns a value of type GEOMETRY.

Geospatial functions , Conversion functions

TO_JSON

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

Conversion functions , Semi-structured and structured data functions

TO_OBJECT

Converts the input value to an OBJECT.

Conversion functions , Semi-structured and structured data functions

TO_QUERY

Returns a result set based on SQL text and an optional set of arguments that are passed to the SQL text if it is parameterized.

Table functions

TO_TIME , TIME

Converts an input expression into a time.

Conversion functions , Date & time functions

TO_TIMESTAMP / TO_TIMESTAMP_*

Converts an input expression into the corresponding timestamp.

Conversion functions , Date & time functions

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.

Conversion functions , Semi-structured and structured data functions

TRANSFORM

Transforms an array based on the logic in a lambda expression.

Semi-structured and structured data functions

TRANSLATE (SNOWFLAKE.CORTEX)

Translates the given input text from one supported language to another.

String & binary functions

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 value to the specified precision.

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_COMPLETE (SNOWFLAKE.CORTEX)

Performs the same operation as the COMPLETE function but returns NULL instead of raising an error when the operation cannot be performed.

String & binary functions

TRY_DECRYPT

A special version of DECRYPT that returns a NULL value if an error occurs during decryption.

Encryption functions

TRY_DECRYPT_RAW

A special version of DECRYPT_RAW that returns a NULL value if an error occurs during decryption.

Encryption 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 and 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 the TO_DATE function 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).

Conversion functions , Date & time functions

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 (that is, converts an input expression to a double-precision floating-point number), but with error-handling support (that is, if the conversion can’t 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.

Geospatial functions , Conversion functions

TRY_TO_GEOMETRY

Parses an input and returns a value of type GEOMETRY.

Geospatial functions , Conversion functions

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

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

Semi-structured and structured data functions

U

UNICODE

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

String & binary functions

UNIFORM

Generates a uniformly-distributed pseudo-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.

String & binary functions , Data generation functions

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

VALIDATE_PIPE_LOAD

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

Information Schema , Table functions

VAR_POP

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

Aggregate functions , Window function syntax and usage

VAR_SAMP

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

Aggregate functions , Window function syntax and usage

VARIANCE , VARIANCE_SAMP

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

Aggregate functions , Window function syntax and usage

VARIANCE_POP

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

Aggregate functions , Window function syntax and usage

VECTOR_COSINE_SIMILARITY

Computes the cosine similarity between two vectors.

Vector similarity functions

VECTOR_INNER_PRODUCT

Computes the inner product of two vectors.

Vector similarity functions

VECTOR_L2_DISTANCE

Computes the L2 distance between two vectors.

Vector similarity functions

W

WAREHOUSE_LOAD_HISTORY

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.

Information Schema , Table functions

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.

Information Schema , Table functions

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 and 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