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 |
||
Returns the absolute value of a numeric expression. |
||
Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval |
||
Computes the inverse (arc) hyperbolic cosine of its input. |
||
Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information. |
||
This INFORMATION_SCHEMA table function can be used to query the history of alerts within a specified date range. |
||
Returns all user names in the current account. |
||
Returns some value of the expression from the group. |
||
Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e. |
||
Returns an approximated value for the desired percentile (i.e. if column |
||
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. |
||
Combines (merges) percentile input states into a single output state. |
||
Returns the desired approximated percentile value for the specified t-Digest state. |
||
Uses Space-Saving to return an approximation of the most frequent values in the input, along with their approximate frequencies. |
||
Returns the Space-Saving summary at the end of aggregation. |
||
Combines (merges) input states into a single output state. |
||
Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. |
||
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. |
||
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. |
||
Returns the input values, pivoted into an ARRAY. |
Aggregate Functions , Window Functions , Semi-structured Data Functions |
|
Returns an array containing all elements from the source array as well as the new element. |
||
Returns a concatenation of two arrays. |
||
Returns a compacted array with missing and null values removed, effectively converting sparse arrays into dense arrays. |
||
Returns an array constructed from zero, one, or more inputs. |
||
Returns an array constructed from zero, one, or more inputs; the constructed array omits any NULL input values. |
||
Returns |
||
Returns a new ARRAY that contains only the distinct elements from the input ARRAY. |
||
Returns a new ARRAY that contains the elements from one input ARRAY that are not in another input ARRAY. |
||
Returns an ARRAY of integer values within a specified range (e.g. |
||
Returns an array containing all elements from the source array as well as the new element. |
||
Returns an array that contains the matching elements in the two input arrays. |
||
Returns the index of the first occurrence of an element in an array. |
||
Returns an array containing the new element as well as all elements from the source array. |
||
Given a source ARRAY, returns an ARRAY with elements of the specified value removed. |
||
Given a source ARRAY, returns an ARRAY with the element at the specified position removed. |
||
Returns the size of the input array. |
||
Returns an array constructed from a specified subset of elements of the input array. |
||
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). |
||
Returns an ARRAY that contains the union of the distinct values from the input ARRAYs in a column. |
||
Returns an ARRAY that contains all of the distinct values from the specified column. |
||
Compares whether two arrays have at least one element in common. |
||
This family of functions can be used to perform strict casting of VARIANT values to other data types. |
||
Casts a VARIANT value to an array. |
||
Casts a VARIANT value to a binary string. |
||
Casts a VARIANT value to a BOOLEAN value. |
||
Casts a VARIANT value to a string. |
||
Casts a VARIANT value to a date. |
||
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale. |
||
Casts a VARIANT value to a floating-point value. |
||
Casts a VARIANT value to an integer. |
||
Casts a VARIANT value to an object. |
||
Casts a VARIANT value to a time value. |
||
Casts a VARIANT value to the respective TIMESTAMP value. |
||
Returns the ASCII code for the first character of a string. |
||
Computes the inverse sine (arc sine) of its argument; the result is a number in the interval |
||
Computes the inverse (arc) hyperbolic sine of its argument. |
||
Computes the inverse tangent (arc tangent) of its argument; the result is a number in the interval |
||
Computes the inverse tangent (arc tangent) of the ratio of its two arguments. |
||
Computes the inverse (arc) hyperbolic tangent of its argument. |
||
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. |
||
This table function is used for querying the Automatic Clustering history for given tables within a specified date range. |
||
Returns the average of non-NULL records. |
||
B |
||
Decodes a Base64-encoded string to a binary. |
||
Decodes a Base64-encoded string to a string. |
||
Encodes the input (string or binary) using Base64 encoding. |
||
Returns |
||
Returns the length of a string or binary value in bits. |
||
Returns the bitwise AND of two numeric expressions. |
||
Returns the bitwise AND value of all non-NULL numeric records in a group. |
Aggregate Functions , Window Functions , Bitwise Expression Functions |
|
Given a numeric value, returns the relative position for the bit that represents that value in a bitmap. |
||
Given a numeric value, returns an identifier (βbucket numberβ) for the bitmap containing the bit that represents the value.. |
||
Returns a bitmap with bits set for each distinct value in a group. |
||
Given a bitmap that represents the set of distinct values for a column, returns the number of distinct value. |
||
Returns a bitmap containing the results of a binary OR operation on the input bitmaps. |
||
Returns the bitwise negation of a numeric expression. |
||
Returns the bitwise OR of two numeric expressions. |
||
Returns the bitwise OR value of all non-NULL numeric records in a group. |
Aggregate Functions , Window Functions , Bitwise Expression Functions |
|
Shifts the bits for a numeric expression |
||
Shifts the bits for a numeric expression |
||
Returns the bitwise XOR of two numeric expressions. |
||
Returns the bitwise XOR value of all non-NULL numeric records in a group. |
Aggregate Functions , Window Functions , Bitwise Expression Functions |
|
Computes the Boolean AND of two numeric expressions. |
||
Returns the logical (boolean) |
Aggregate Functions , Window Functions , Conditional Expression Functions |
|
Computes the Boolean NOT of a single numeric expression. |
||
Computes the Boolean OR of two numeric expressions. |
||
Returns the logical (boolean) |
Aggregate Functions , Window Functions , Conditional Expression Functions |
|
Computes the Boolean XOR of two numeric expressions (i.e. one of the expressions, but not both expressions, is TRUE). |
||
Returns the logical (boolean) |
Aggregate Functions , Window Functions , Conditional Expression Functions |
|
Generates a scoped Snowflake-hosted URL to a staged file using the stage name and relative file path as inputs. |
||
Generates a Snowflake-hosted file URL to a staged file using the stage name and relative file path as inputs. |
||
C |
||
Works like a cascading βif-then-elseβ statement. |
||
Converts a value of one data type into another data type. |
||
Returns the cubic root of a numeric expression. |
||
Returns values from |
||
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. |
||
Checks the validity of a JSON document. |
||
Checks the validity of an XML document. |
||
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. |
||
Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL. |
||
Returns a copy of the original string, but with the specified |
||
Returns the collation specification of the expression. |
||
Returns the status of a completed graph run. |
||
Compresses the input string or binary value with a compression method. |
||
Concatenates one or more strings, or concatenates one or more binary values. |
||
Concatenates two or more strings, or concatenates two or more binary values. |
||
Returns a window event number for each row within a window partition when the value of the argument |
||
Returns a window event number for each row within a window partition based on the result of the boolean argument |
||
Returns true if |
||
Converts a timestamp to another time zone. |
||
This table function can be used to query Snowflake data loading history along various dimensions within the last 14 days. |
||
Returns the correlation coefficient for non-null pairs in a group. |
||
Computes the cosine of its argument; the argument should be expressed in radians. |
||
Computes the hyperbolic cosine of its argument. |
||
Computes the cotangent of its argument; the argument should be expressed in radians. |
||
Returns either the number of non-NULL records for the specified columns, or the total number of records. |
||
Returns the number of records that satisfy a condition. |
||
Returns the population covariance for non-null pairs in a group. |
||
Returns the sample covariance for non-null pairs in a group. |
||
Finds the cumulative distribution of a value with regard to other values within the same window partition. |
||
Returns the account locator used by the userβs current session. |
||
Returns a string (VARCHAR) that lists all account-level roles granted to the current user. |
||
Returns the version of the client from which the function was called. |
||
Returns the name of the database in use for the current session. |
||
Returns the current date of the system. |
||
Returns the IP address of the client that submitted the request. |
||
Returns the name of the region for the account where the current user is logged in. |
||
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. |
||
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. |
||
Returns the name of the schema in use by the current session. |
||
Returns active search path schemas. |
||
Returns the name of the secondary roles in use for the current session. |
||
Returns a unique system identifier for the Snowflake session corresponding to the present connection. |
||
Returns the SQL text of the statement that is currently executing. |
||
Returns the status of a graph run that is currently scheduled or is executing. |
||
Returns the current time for the system. |
||
Returns the current timestamp for the system. |
||
Returns the transaction id of an open transaction in the current session. |
||
Returns the name of the user currently logged into the system. |
||
Returns the current Snowflake version. |
||
Returns the name of the warehouse in use for the current session. |
||
D |
||
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. |
||
Returns the refresh history for a secondary database. |
||
DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB |
The DATABASE_REFRESH_PROGRESS family of functions can be used to query the status of a database refresh along various dimensions. |
|
This table function can be used to query the replication history for a specified database within a specified date range. |
||
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. |
||
Creates a date from individual numeric components that represent the year, month, and day of the month. |
||
Extracts the specified date or time part from a date, time, or timestamp. |
||
Truncates a |
||
Adds the specified value for the specified date or time part to a date, time, or timestamp. |
||
Calculates the difference between two date, time, or timestamp expressions based on the date or time part requested. |
||
Extracts the three-letter day-of-week name from the specified date or timestamp. |
||
Compares the select expression to each search expression in order. |
||
Decompresses the compressed |
||
Decompresses the compressed |
||
Decrypts a BINARY value using a VARCHAR passphrase. |
||
Decrypts a BINARY value using a BINARY key. |
||
Converts radians to degrees. |
||
Returns the rank of a value within a group of values, without gaps in the ranks. |
||
Performs division like the division operator ( |
||
Performs division like the division operator ( |
||
E |
||
Computes the Levenshtein distance between two input strings. |
||
Encrypts a VARCHAR or BINARY value using a VARCHAR passphrase. |
||
Encrypts a BINARY value using a BINARY key. |
||
Returns TRUE if the first expression ends with second expression. |
||
Compares whether two expressions are equal. |
||
Computes Eulerβs number |
||
This function converts an EXPLAIN plan from JSON to a table. |
||
This table function retrieves the history of external functions called by Snowflake for your entire Snowflake account. |
||
This table function can be used to query information about the staged data files included in the metadata for a specified external table. |
||
This table function can be used to query information about the metadata history for an external table. |
||
Extracts the specified date or time part from a date, time, or timestamp. |
||
Returns a set of categories (semantic and privacy) for each supported column in the specified table or view. |
||
F |
||
Computes the factorial of its input. |
||
Returns the first value within an ordered group of values. |
||
Flattens (explodes) compound values into multiple rows. |
||
Returns values from |
||
G |
||
Generates a list of columns from a set of staged files that contain semi-structured data using the INFER_SCHEMA function output. |
||
Creates rows of data based either on a specified number of rows, a specified generation period (in seconds), or both. |
||
Extracts a value from an ARRAY or an OBJECT (or a VARIANT that contains an ARRAY or OBJECT). |
||
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. |
||
Returns a DDL statement that can be used to recreate the specified object. |
||
Extracts a field value from an object; returns NULL if either of the arguments is NULL. |
||
Returns a list of objects that a specified object references. |
||
Extracts a value from semi-structured data using a path name. |
||
Generates the pre-signed URL to a staged file using the stage name and relative file path as inputs. |
||
Returns statistics about individual query operators within a query that has completed. |
||
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. |
||
Retrieves the URL for an external or internal named stage using the stage name as the input. |
||
Given an INTEGER value, returns the value of a bit at a specified position. |
||
Returns the largest value from a list of expressions. |
||
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query. |
||
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query. |
||
H |
||
Returns a signed 64-bit hash value. |
||
Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. |
||
Calculates the great circle distance in kilometers between two points on the Earthβs surface, using the Haversine formula. |
||
Decodes a hex-encoded string to a binary. |
||
Decodes a hex-encoded string to a string. |
||
Encodes the input using hexadecimal (also βhexβ or βbase16β) encoding. |
||
Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e. |
||
Returns the HyperLogLog state at the end of aggregation. |
||
Combines (merges) input states into a single output state. |
||
Returns the cardinality estimate for the given HyperLogLog state. |
||
Converts input in BINARY format to OBJECT format. |
||
Converts input in OBJECT format to BINARY format. |
||
Extracts the corresponding time part from a time or timestamp value. |
||
I |
||
Single-level |
||
If |
||
Allows matching of strings based on comparison with a pattern. |
||
Allows case-insensitive matching of strings based on comparison with one or more patterns. |
||
Tests whether its argument is or is not one of the members of an explicit list or the result of a subquery. |
||
Automatically detects the file metadata schema in a set of staged data files that contain semi-structured data and retrieves the column definitions. |
||
Returns the input string ( |
||
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value. |
||
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. |
||
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. |
||
Compares whether two expressions are equal (or not equal). |
||
Determines whether an expression is NULL or is not NULL. |
||
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. |
||
Returns TRUE if its VARIANT argument contains an ARRAY value. |
||
Returns TRUE if its VARIANT argument contains a binary string. |
||
Returns TRUE if its VARIANT argument contains a Boolean value. |
||
Returns TRUE if its VARIANT argument contains a string value. |
||
Verifies whether a VARIANT value contains a DATE value. |
||
Returns TRUE if its VARIANT argument contains a fixed-point decimal value or integer. |
||
Returns TRUE if its VARIANT argument contains a floating-point value, fixed-point decimal, or integer. |
||
Returns TRUE if the role returned by the INVOKER_ROLE function inherits the privileges of the specified role in the argument. |
||
Returns TRUE if its VARIANT argument contains an integer value. |
||
Returns true if its VARIANT argument is a JSON null value. |
Conditional Expression Functions , Semi-structured Data Functions |
|
Returns TRUE if its VARIANT argument contains an OBJECT value. |
||
Verifies whether the argument value (i.e. role) is in the userβs active primary or secondary role hierarchy for the session or if the specified column contains a role that is in the userβs active primary or secondary role hierarchy for the session. |
||
Verifies whether a VARIANT value contains a TIME value. |
||
Verifies whether a VARIANT value contains the respective TIMESTAMP value. |
||
J |
||
Computes the Jaro-Winkler similarity between two input strings. |
||
Parses the first argument as a JSON string and returns the value of the element pointed to by the path in the second argument. |
||
K |
||
Returns the population excess kurtosis of non-NULL records. |
||
L |
||
Accesses data in a previous row in the same result set without having to join the table to itself. |
||
Returns the last day of the specified date part for a date or timestamp. |
||
Returns the ID of a specified query in the current session. |
||
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. |
||
Returns the transaction ID of the last transaction that was either committed or rolled back in the current session. |
||
Returns the last value within an ordered group of values. |
||
Accesses data in a subsequent row in the same result set without having to join the table to itself. |
||
Returns the smallest value from a list of expressions. |
||
Returns a leftmost substring of its input. |
||
Returns the length of an input string or binary value. |
||
Allows case-sensitive matching of strings based on comparison with a pattern. |
||
Allows case-sensitive matching of strings based on comparison with one or more patterns. |
||
Allows case-sensitive matching of strings based on comparison with one or more patterns. |
||
Returns the concatenated input values, separated by the |
||
Returns the natural logarithm of a numeric expression. |
||
Returns the current time for the system. |
||
Returns the current timestamp for the system. |
||
Returns the logarithm of a numeric expression. |
||
The LOGIN_HISTORY family of table functions can be used to query login attempts by Snowflake users along various dimensions. |
||
Returns the input string ( |
||
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value. |
||
Removes leading characters, including whitespace, from a string. |
||
M |
||
This table function is used for querying the materialized views refresh history for a specified materialized view within a specified date range. |
||
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest. |
||
Returns a 16-byte |
||
Returns the 128-bit MD5 message digest interpreted as a signed 128-bit big endian number. |
||
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. |
||
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. |
||
Determines the median of a set of values. |
||
Returns the minimum or maximum value for the records within |
||
Finds the row(s) containing the minimum or maximum value for a column and returns the value of another column in that row. |
||
Returns a MinHash state containing an array of size |
||
Combines input MinHash states into a single MinHash output state. |
||
Returns the remainder of input |
||
Returns the most frequent value for the values within |
||
Extracts the three-letter month name from the specified date or timestamp. |
||
Returns the number of months between two DATE or TIMESTAMP values. |
||
N |
||
Returns the date of the first specified DOW (day of week) that occurs after the input date. |
||
Generates a normally-distributed pseudo-random floating point number with specified |
||
This table function can be used to query the history of notifications sent through Snowflake. |
||
Returns the nth value (up to 1000) within an ordered group of values. |
||
Divides an ordered data set equally into the number of buckets specified by |
||
Returns NULL if |
||
Returns NULL if the argument evaluates to |
||
If |
||
Returns values depending on whether the first input is NULL. |
||
O |
||
Returns one OBJECT per group. |
Aggregate Functions , Window Functions , Semi-structured Data Functions |
|
Returns an OBJECT constructed from the arguments. |
||
Returns an OBJECT constructed from the arguments. |
||
Returns an object containing the contents of the input (i.e.source) object with one or more keys removed. |
||
Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value). |
||
Returns an array containing the list of keys in the top-most level of the input object. |
||
Returns a new OBJECT containing some of the key-value pairs from an existing object. |
||
Returns the length of a string or binary value in bytes. |
||
P |
||
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. |
||
Interprets an input string as a JSON document, producing a VARIANT value. |
||
Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI. |
||
Interprets an input string as an XML document, producing an OBJECT value. |
||
Returns the relative rank of a value within a group of values, specified as a percentage ranging from 0.0 to 1.0. |
||
Return a percentile value based on a continuous distribution of the input column (specified in |
||
Returns a percentile value based on a discrete distribution of the input column (specified in |
||
Returns the value of pi as a floating-point value. |
||
This table function can be used to query the history of data loaded into Snowflake tables using Snowpipe within a specified date range. |
||
Simulates the query result for table or view columns protected by a masking policy, a table or view protected by a row access policy, or both if the table or view is protected by both types of policies. |
||
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. |
||
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. |
||
Returns a number (x) raised to the specified power (y). |
||
Returns the date of the first specified DOW (day of week) that occurs before the input date. |
||
Q |
||
The QUERY_ACCELERATION_HISTORY function is used for querying the query acceleration service history within a specified date range. |
||
The QUERY_HISTORY family of table functions can be used to query Snowflake query history along various dimensions. |
||
R |
||
Converts degrees to radians. |
||
Each call returns a pseudo-random 64-bit integer. |
||
Returns a random string of specified |
||
Returns the rank of a value within an ordered group of values. |
||
Returns the ratio of a value within a group to the sum of the values within the group. |
||
Returns true if the subject matches the specified pattern. |
||
Returns the number of times that a pattern occurs in a string. |
||
Returns the position of the specified occurrence of the regular expression pattern in the string subject. |
||
Returns true if the subject matches the specified pattern. |
||
Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. |
||
Returns the substring that matches a regular expression within a string. |
||
Returns an ARRAY that contains all substrings that match a regular expression within a string. |
||
Returns the average of the independent variable for non-null pairs in a group, where |
||
Returns the average of the dependent variable for non-null pairs in a group, where |
||
Returns the number of non-null number pairs in a group. |
||
Returns the intercept of the univariate linear regression line for non-null pairs in a group. |
||
Returns the coefficient of determination for non-null pairs in a group. |
||
Returns the slope of the linear regression line for non-null pairs in a group. |
||
Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs. |
||
Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs. |
||
Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs. |
||
Returns NULL if the first argument is NULL; otherwise, returns the second argument. |
||
Returns NULL if the second argument is NULL; otherwise, returns the first argument. |
||
Builds a string by repeating the input for the specified number of times. |
||
Removes all occurrences of a specified substring, and optionally replaces them with another string. |
||
Returns the replication history for a secondary replication or failover group within the last 14 days. |
||
REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB |
The REPLICATION_GROUP_REFRESH_PROGRESS family of functions can be used to query the status of a replication or failover group refresh. |
|
Returns the replication usage history for secondary replication or failover groups within the last 14 days. |
||
This table function can be used to query the replication history for a specified database within a specified date range. |
||
Returns a list of SCIM REST API requests made to Snowflake over a specified time interval. |
||
Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table. |
||
Reverses the order of characters in a string, or of bytes in a binary value. |
||
Returns a rightmost substring of its input. |
||
Returns true if the subject matches the specified pattern. |
||
Returns rounded values for |
||
Returns a unique row number for each row within a window partition. |
||
Right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value. |
||
Removes trailing characters, including whitespace, from a string. |
||
Returns the length of its argument, minus trailing whitespace, but including leading whitespace. |
||
S |
||
Returns the timestamp representing the scheduled time of the current alert. |
||
This table function is used for querying the search optimization service maintenance history for a specified table within a specified date range. |
||
Returns a sequence of monotonically increasing integers, with wrap-around. |
||
This table function is used for querying the serverless task usage history. |
||
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest. |
||
Returns a 20-byte binary containing the 160-bit SHA-1 message digest. |
||
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size. |
||
Returns a binary containing the N-bit SHA-2 message digest, where N is the specified output digest size. |
||
Returns the sign of its argument. |
||
Computes the sine of its argument; the argument should be expressed in radians. |
||
Computes the hyperbolic sine of its argument. |
||
Returns the sample skewness of non-NULL records. |
||
Returns a string that contains a phonetic representation of the input string. |
||
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. |
||
Builds a string consisting of the specified number of blank spaces. |
||
Splits a given string with a given separator and returns the result in an array of strings. |
||
Splits a given string at a specified character and returns the requested part. |
||
This table function splits a string (based on a specified delimiter) and flattens the results into rows. |
||
Returns the square-root of a non-negative numeric expression. |
||
Returns the square of a numeric expression (i.e. a numeric expression multiplied by itself). |
||
Returns the area of the Polygon(s) in a GEOGRAPHY or GEOMETRY object. |
||
Given a value of type GEOGRAPHY or GEOMETRY, return the binary representation of that value in EWKB (extended well-known binary) format. |
||
Given a value of type GEOGRAPHY or GEOMETRY, return the text (VARCHAR) representation of that value in EWKT (extended well-known text) format. |
||
Given a value of type GEOGRAPHY or GEOMETRY, return the GeoJSON representation of that value. |
||
Given a value of type GEOGRAPHY or GEOMETRY, return the binary representation of that value in WKB (well-known binary) format. |
||
Given a value of type GEOGRAPHY or GEOMETRY, return the text (VARCHAR) representation of that value in WKT (well-known text) format. |
||
Given a Point that represents the origin (the location of the observer) and a specified Point, returns the azimuth in radians. |
||
Returns a GEOMETRY object that represents a MultiPolygon containing the points within a specified distance of the input GEOMETRY object. |
||
Returns the Point representing the geometric center of a GEOGRAPHY or GEOMETRY object. |
||
There are two forms of ST_COLLECT. |
||
Returns TRUE if a GEOGRAPHY or GEOMETRY object is completely inside another object of the same type. |
||
Returns TRUE if no point in one geospatial object is outside another geospatial object. |
||
Returns TRUE if no point in one geospatial object is outside of another geospatial object. |
||
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). |
||
Given a value of type GEOGRAPHY or GEOMETRY, return the βdimensionβ of the value. |
||
Returns TRUE if the two GEOGRAPHY objects or the two GEOMETRY objects are disjoint (i.e. do not share any portion of space). |
||
Returns the minimum geodesic distance between two GEOGRAPHY or the minimum Euclidean distance between two GEOMETRY objects. |
||
Returns TRUE if the minimum geodesic distance between two points (two GEOGRAPHY objects) is within the specified distance. |
||
Returns the last Point in a LineString. |
||
Returns the minimum bounding box (a rectangular βenvelopeβ) that encloses a specified GEOGRAPHY or GEOMETRY object. |
||
Returns a GEOGRAPHY object for the polygon that represents the boundaries of a geohash. |
||
Returns a GEOGRAPHY object for the Point that represents the center of a geohash. |
||
Parses a WKB (well-known binary) or EWKB (extended well-known binary) input and returns a value of type GEOGRAPHY. |
||
Parses a WKT (well-known text) or EWKT (extended well-known text) input and returns a value of type GEOGRAPHY. |
||
Parses a WKB (well-known binary) or EWKB (extended well-known binary) input and returns a value of type GEOMETRY. |
||
Parses a WKT (well-known text) or EWKT (extended well-known text) input and returns a value of type GEOMETRY. |
||
Returns the discrete Hausdorff distance between two GEOGRAPHY objects. |
||
Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the set of points that are common to both input objects (i.e. the intersection of the two objects). |
||
Returns TRUE if the two GEOGRAPHY objects or the two GEOMETRY objects intersect (i.e. share any portion of space). |
||
Returns TRUE if the specified GEOGRAPHY or GEOMETRY object represents a valid shape. |
||
Returns the geodesic length of the LineString(s) in a GEOGRAPHY object or the Euclidean length of the LineString(s) in a GEOMETRY object. |
||
Constructs a GEOMETRY object that represents a Point with the specified longitude and latitude. |
||
Constructs a GEOGRAPHY or GEOMETRY object that represents a line connecting the points in the input objects. |
||
Constructs a GEOGRAPHY object that represents a point with the specified longitude and latitude. |
||
Constructs a GEOGRAPHY or GEOMETRY object that represents a Polygon without holes. |
||
Constructs a GEOGRAPHY object that represents a Polygon without holes. |
||
Returns the number of points in a GEOGRAPHY or GEOGRAPHY object. |
||
Returns the length of the perimeter of the polygon(s) in a GEOGRAPHY or GEOMETRY object. |
||
Returns a Point at a specified index in a LineString. |
||
Returns a GEOMETRY object that has its SRID (spatial reference system identifier) set to the specified value. |
||
Given an input GEOGRAPHY or GEOMETRY object that represents a Line or Polygon, returns a simpler approximation of the object. |
||
Returns the SRID (spatial reference system identifier) of a GEOGRAPHY or GEOMETRY object. |
||
Returns the first Point in a LineString. |
||
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). |
||
Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the combined set of all points from both objects (i.e. the union of the two objects). |
||
Returns true if the first geospatial object is fully contained by the second geospatial object. |
||
Returns the longitude (X coordinate) of a Point represented by a GEOGRAPHY or GEOMETRY object. |
||
Returns the maximum longitude (X coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object. |
||
Returns the minimum longitude (X coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object. |
||
Returns the latitude (Y coordinate) of a Point represented by a GEOGRAPHY or GEOMETRY object. |
||
Returns the maximum latitude (Y coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object. |
||
Returns the minimum latitude (Y coordinate) of all points contained in the specified GEOGRAPHY or GEOMETRY object. |
||
This table function can be used to query information about the metadata history for a directory table. |
||
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. |
||
Returns true if |
||
Returns the sample standard deviation (square root of sample variance) of non-NULL values. |
||
Returns the population standard deviation (square root of variance) of non-NULL values. |
||
Returns the sample standard deviation (square root of sample variance) of non-NULL values. |
||
Converts a JSON βnullβ value to a SQL NULL value. |
||
Tokenizes a given string and returns the requested part. |
||
Tokenizes a string with the given set of delimiters and flattens the results into rows. |
||
Tokenizes the given string using the given set of delimiters and returns the tokens as an array. |
||
Returns the portion of the string or binary value from |
||
Returns the sum of non-NULL records for |
||
Returns the current timestamp for the system, but in the UTC time zone. |
||
Aborts the specified session. |
||
Aborts the specified transaction, if it is running. |
||
Add an event for trace. |
||
Returns hostnames and port numbers to add to your firewallβs allowed list so that you can access Snowflake from behind your firewall. |
||
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. |
||
Enables private connectivity to the Snowflake service for the current account. |
||
Authorizes Snowflake to access the Microsoft Azure Private Endpoint for Azure Private Endpoints for Internal Stages for the current account. |
||
Returns the status of the specified behavior change release bundle for the current account. |
||
Cancels all active/running queries in the specified session. |
||
Cancels the specified query (or statement) if it is currently active/running. |
||
Computes the average depth of the table according to the specified columns (or the clustering key defined for the table). |
||
Returns clustering information, including average clustering depth, for a table based on one or more columns in the table. |
||
Calculates the clustering ratio for a table, based on one or more columns in the table. |
||
Returns the name of the task currently executing when invoked from the statement or stored procedure defined by the task. |
||
Returns a JSON object showing the refresh history for a secondary database. |
||
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. |
|
Disables the behavior changes included in the specified release bundle for the current account. |
||
Disable replication for a primary database and any secondary databases linked to it. |
||
Enables behavior changes included in the specified release bundle for the current account. |
||
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. |
||
Returns the estimated costs of adding search optimization to a given table and configuring specific columns for search optimization. |
||
This function converts EXPLAIN output from JSON to formatted text. |
||
Given the text of a SQL statement, this function generates the EXPLAIN plan in JSON. |
||
Retrieves a JSON representation of the current refresh status for the internal (hidden) pipe object associated with an external table. |
||
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 |
||
Returns a new SCIM access token that is valid for six months. |
||
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. |
||
Returns a JSON object that represents an unsuccessful login attempt associated with External OAuth or SAML. |
||
Retrieves the return value for the predecessor task in a DAG of tasks. |
||
Verifies whether your current account is authorized for private connectivity to the Snowflake service. |
||
Returns a list of the authorized endpoints for your current account to use with private connectivity to the Snowflake service. |
||
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. |
||
Returns the IDs of the virtual network in which your Snowflake account is located. |
||
Returns the tag value associated with the specified Snowflake object or column. |
||
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. |
||
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. |
||
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. |
||
Enables replication and failover features for a specified account in an organization. |
||
Returns a token that can be used to detect whether a database table or view changed between two calls to the function. |
||
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. |
||
Logs a message at the specified severity level. |
||
Migrates an existing SAML identity provider (i.e. IdP) configuration as defined by the account parameter SAML_IDENTITY_PROVIDER to a security integration. |
||
Forces a pipe paused using ALTER PIPE to resume. |
||
Retrieves a JSON representation of the current status of a pipe. |
||
Disables private connectivity to the Snowflake service for the current account. |
||
Revokes the authorization for Snowflake to access the Microsoft Azure Private Endpoint for Azure Private Endpoints for Internal Stages for the current account. |
||
Explicitly sets the return value for a task. |
||
Sets attribute name and value associated with a span containing trace events. |
||
Returns the client secrets in a string. |
||
Returns the set of table versions between the current offset for a specified stream and the current timestamp. |
||
Returns the timestamp in nanoseconds of the latest table version at or before the current offset for the specified stream. |
||
Indicates whether a specified stream contains change data capture (CDC) records. |
||
Recursively resumes all dependent tasks tied to a specified root task. |
||
Returns a string representing the SQL data type associated with an expression. |
||
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). |
||
Determines whether your External OAuth access token is valid or has expired and needs to be regenerated. |
||
Waits for the specified amount of time before proceeding. |
||
Returns hostnames and port numbers to add to your firewallβs allowed list so that you can access Snowflake from behind your firewall. |
||
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. |
||
T |
||
Returns a table in which each row displays an association between a tag and value. |
||
Returns a table in which each row displays the tag name and tag value assigned to a specific column. |
||
Returns a table in which each row displays an association between the specified tag and the Snowflake object to which the tag is associated. |
||
Computes the tangent of its argument; the argument should be expressed in radians. |
||
Computes the hyperbolic tangent of its argument. |
||
This table function returns the list of child tasks for a given root task in a DAG of tasks. |
||
This table function can be used to query the history of task usage within a specified date range. |
||
Creates a time from individual numeric components. |
||
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.). |
||
Adds the specified value for the specified date or time part to a date, time, or timestamp. |
||
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part. |
||
Creates a timestamp from individual numeric components. |
||
Adds the specified value for the specified date or time part to a date, time, or timestamp. |
||
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part. |
||
Converts the input expression to an ARRAY. |
||
Converts the input expression to a binary value. |
||
Coverts the input text or numeric expression to a Boolean value. |
||
Converts the input expression to a string. |
||
Converts an input expression to a date. |
||
Converts an input expression to a fixed-point number. |
||
Converts an expression to a double-precision floating-point number. |
||
Parses an input and returns a value of type GEOGRAPHY. |
||
Parses an input and returns a value of type GEOMETRY. |
||
Converts a VARIANT value to a string containing the JSON representation of the value. |
||
Converts the input value to an OBJECT. |
||
Converts an input expression into a time. |
||
Converts an input expression into the corresponding timestamp. |
||
Converts any value to a VARIANT value or NULL (if input is NULL). |
||
Converts a VARIANT to a VARCHAR that contains an XML representation of the value. |
||
The |
||
Removes leading and trailing characters from a string. |
||
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. |
||
Truncates a date, time, or timestamp to the specified part. |
||
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding. |
||
A special version of BASE64_DECODE_STRING that returns a NULL value if an error occurs during decoding. |
||
A special version of CAST , :: that is available for a subset of data type conversions. |
||
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding. |
||
A special version of HEX_DECODE_STRING that returns a NULL value if an error occurs during decoding. |
||
A special version of PARSE_JSON that returns a NULL value if an error occurs during parsing. |
||
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). |
||
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). |
||
A special version of TO_DATE , DATE that performs the same operation (i.e. converts an input expression to a date), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error). |
||
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). |
||
A special version of TO_DOUBLE that performs the same operation (i.e. converts an input expression to a double-precision floating-point number), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error). |
||
Parses an input and returns a value of type GEOGRAPHY. |
||
Parses an input and returns a value of type GEOMETRY. |
||
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). |
||
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). |
||
Reports the type of a value stored in a VARIANT column. |
||
U |
||
Returns the Unicode code point for the first Unicode character in a string. |
||
Generates a uniformly-distributed pseudo-random number in the inclusive range [ |
||
Returns the input string |
||
Generates either a version 4 (random) or version 5 (named) RFC 4122-compliant UUID as a formatted string. |
||
V |
||
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. |
||
This table function can be used to validate data files processed by Snowpipe within a specified time range. |
||
Returns the population variance of non-NULL records in a group. |
||
Returns the sample variance of non-NULL records in a group. |
||
Returns the sample variance of non-NULL records in a group. |
||
Returns the population variance of non-NULL records in a group. |
||
W |
||
This table function can be used to query the activity history (defined as the βquery loadβ) for a single warehouse within a specified date range. |
||
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. |
||
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. |
||
X |
||
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. |
||
Y |
||
Extracts the corresponding date part from a date or timestamp. |
||
Z |
||
Returns 0 if its argument is null; otherwise, returns its argument. |
||
Returns a Zipf-distributed integer, for |