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 [-pi, 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

ANY_VALUE

Returns some value of the expression from the group.

Aggregate Functions , Window Functions

APPROX_COUNT_DISTINCT

Uses HyperLogLog to return an approximation of the distinct cardinality of the input.

Aggregate Functions , Window Functions

APPROX_PERCENTILE

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

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 Functions

APPROX_PERCENTILE_COMBINE

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

Aggregate Functions , Window Functions

APPROX_PERCENTILE_ESTIMATE

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

Aggregate Functions , Window Functions

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 Functions

APPROX_TOP_K_COMBINE

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

Aggregate Functions , Window Functions

APPROX_TOP_K_ESTIMATE

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

Aggregate Functions , Window Functions

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 Functions

ARRAY_AGG

Returns the input values, pivoted into an ARRAY.

Aggregate Functions , Window Functions , Semi-structured Data Functions

ARRAY_APPEND

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

Semi-structured Data Functions

ARRAY_CAT

Returns a concatenation of two arrays.

Semi-structured Data Functions

ARRAY_COMPACT

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

Semi-structured Data Functions

ARRAY_CONSTRUCT

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

Semi-structured Data Functions

ARRAY_CONSTRUCT_COMPACT

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

Semi-structured Data Functions

ARRAY_CONTAINS

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

Semi-structured Data Functions

ARRAY_INSERT

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

Semi-structured Data Functions

ARRAY_POSITION

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

Semi-structured Data Functions

ARRAY_PREPEND

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

Semi-structured Data Functions

ARRAY_SIZE

Returns the size of the input array.

Semi-structured Data Functions

ARRAY_SLICE

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

Semi-structured Data Functions

ARRAY_TO_STRING

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

Semi-structured Data Functions

ARRAYS_OVERLAP

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

Semi-structured Data Functions

AS_<object_type>

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

Semi-structured Data Functions

AS_ARRAY

Casts a VARIANT value to an array.

Semi-structured Data Functions

AS_BINARY

Casts a VARIANT value to a binary string.

Semi-structured Data Functions

AS_BOOLEAN

Casts a VARIANT value to a BOOLEAN value.

Semi-structured Data Functions

AS_CHAR , AS_VARCHAR

Casts a VARIANT value to a string.

Semi-structured Data Functions

AS_DATE

Casts a VARIANT value to a date.

Semi-structured Data Functions

AS_DECIMAL , AS_NUMBER

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

Semi-structured Data Functions

AS_DOUBLE , AS_REAL

Casts a VARIANT value to a floating-point value.

Semi-structured Data Functions

AS_INTEGER

Casts a VARIANT value to an integer.

Semi-structured Data Functions

AS_OBJECT

Casts a VARIANT value to an object.

Semi-structured Data Functions

AS_TIME

Casts a VARIANT value to a time value.

Semi-structured Data Functions

AS_TIMESTAMP_*

Casts a VARIANT value to the respective TIMESTAMP value.

Semi-structured Data Functions

ASCII

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

String & Binary Functions

ASIN

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

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 (i.e. ATAN2(x,y) = ATAN(x/y)).

Numeric Functions

ATANH

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

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

BITNOT

Returns the bitwise negation of a numeric expression.

Bitwise Expression Functions

BITOR

Returns the bitwise OR of two numeric expressions.

Bitwise Expression Functions

BITOR_AGG

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

Aggregate Functions , Window Functions , Bitwise Expression Functions

BITSHIFTLEFT

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

Bitwise Expression Functions

BITSHIFTRIGHT

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

Bitwise Expression Functions

BITXOR

Returns the bitwise XOR of two numeric expressions.

Bitwise Expression Functions

BITXOR_AGG

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

Aggregate Functions , Window Functions , Bitwise Expression Functions

BOOLAND

Computes the Boolean AND of two numeric expressions.

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

BOOLXOR

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

Conditional Expression Functions

C

CASE

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

Conditional Expression Functions

CAST , ::

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

Conversion Functions

CBRT

Returns the cubic root of a numeric expression.

Numeric Functions

CEIL

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

Numeric Functions

CHARINDEX

Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.

String & Binary Functions

CHECK_JSON

Checks the validity of a JSON document.

Semi-structured Data Functions

CHECK_XML

Checks the validity of an XML document.

Semi-structured Data Functions

CHR , CHAR

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

String & Binary Functions

COALESCE

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

Conditional Expression Functions

COLLATE

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

String & Binary Functions

COLLATION

Syntax ———————————————————————-

String & Binary Functions

COMPRESS

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

String & Binary Functions

CONCAT , ||

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

String & Binary 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.

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

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.

Aggregate Functions , Window Functions

CURRENT_ACCOUNT

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

Context Functions

CURRENT_CLIENT

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

Context Functions

CURRENT_DATABASE

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

Context Functions

CURRENT_DATE

Returns the current date of the system.

Context Functions

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 role in use for the current session.

Context Functions

CURRENT_SCHEMA

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

Context Functions

CURRENT_SCHEMAS

Returns active search path schemas.

Context Functions

CURRENT_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_TIME

Returns the current time for the system.

Context Functions

CURRENT_TIMESTAMP

Returns the current timestamp for the system.

Context Functions

CURRENT_TRANSACTION

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

Context Functions

CURRENT_USER

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

Context Functions

CURRENT_VERSION

Returns the current Snowflake version.

Context Functions

CURRENT_WAREHOUSE

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

Context Functions

D

DATA_TRANSFER_HISTORY

This table function can be used to query the history of data transferred from Snowflake tables into a different cloud storage provider’s network (i.e. from Snowflake on AWS 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

This table function returns the refresh history for a secondary database (for database replication).

Information Schema , Table Functions

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

This family of functions can be used to query the status of a database refresh along various dimensions (for database replication).

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

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.

Aggregate Functions , Window Functions

E

EDITDISTANCE

Computes the Levenshtein distance between two input strings.

String & Binary Functions

ENDSWITH

Returns TRUE if the first expression ends with second expression.

String & Binary Functions

EQUAL_NULL

Compares whether two expressions are equal.

Conditional Expression Functions

EXP

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

Numeric Functions

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

F

FACTORIAL

Computes the factorial of its input.

Numeric Functions

FIRST_VALUE

Returns the first value within an ordered group of values.

Aggregate Functions , Window Functions

FLATTEN

Flattens (explodes) compound values into multiple rows.

Table Functions , Semi-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

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 object or array (matches are case-sensitive); returns NULL if either of the arguments is NULL.

Semi-structured Data Functions

GET_DDL

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

Utility & Hash Functions

GET_IGNORE_CASE

Extracts a value from an object or array (matches are case-insensitive); returns NULL if either of the arguments is NULL.

Semi-structured Data Functions

GET_OBJECT_REFERENCES

Returns a list of objects that a specified object references.

Table Functions

GET_PATH , :

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

Semi-structured Data Functions

GREATEST

Returns the largest value from a list of expressions.

Conditional Expression Functions

GROUPING

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

Aggregate Functions

GROUPING_ID

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

Aggregate Functions

H

HASH

Returns a signed 64-bit hash value.

Utility & Hash Functions

HASH_AGG

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

Aggregate Functions , Window Functions , Utility & Hash Functions

HAVERSINE

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

Numeric Functions

HEX_DECODE_BINARY

Decodes a hex-encoded string to a binary.

String & Binary Functions

HEX_DECODE_STRING

Decodes a hex-encoded string to a string.

String & Binary Functions

HEX_ENCODE

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

String & Binary Functions

HLL

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

Aggregate Functions , Window Functions

HLL_ACCUMULATE

Returns the HyperLogLog state at the end of aggregation.

Aggregate Functions , Window Functions

HLL_COMBINE

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

Aggregate Functions , Window Functions

HLL_ESTIMATE

Returns the cardinality estimate for the given HyperLogLog state.

Aggregate Functions , Window Functions

HLL_EXPORT

Converts input in BINARY format to OBJECT format.

Aggregate Functions , Window Functions

HLL_IMPORT

Converts input in OBJECT format to BINARY format.

Aggregate Functions , Window Functions

HOUR / MINUTE / SECOND

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

Date & Time Functions

I

IFF

Single-level if-then-else expression.

Conditional Expression Functions

IFNULL

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

Conditional Expression Functions

ILIKE

Allows matching of strings based on comparison with a pattern.

String & Binary Functions

[ 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

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

IS [ NOT ] DISTINCT FROM

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

Conditional Expression Functions

IS [ NOT ] NULL

Determines whether an expression is NULL or is not NULL.

Conditional Expression Functions

IS_<object_type>

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

Semi-structured Data Functions

IS_ARRAY

Returns TRUE if its VARIANT argument contains an ARRAY value.

Semi-structured Data Functions

IS_BINARY

Returns TRUE if its VARIANT argument contains a binary string.

Semi-structured Data Functions

IS_BOOLEAN

Returns TRUE if its VARIANT argument contains a Boolean value.

Semi-structured Data Functions

IS_CHAR , IS_VARCHAR

Returns TRUE if its VARIANT argument contains a string value.

Semi-structured Data Functions

IS_DATE , IS_DATE_VALUE

Verifies whether a VARIANT value contains a DATE value.

Semi-structured Data Functions

IS_DECIMAL

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

Semi-structured Data Functions

IS_DOUBLE , IS_REAL

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

Semi-structured Data Functions

IS_INTEGER

Returns TRUE if its VARIANT argument contains an integer value.

Semi-structured Data Functions

IS_NULL_VALUE

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

Conditional Expression Functions , Semi-structured Data Functions

IS_OBJECT

Returns TRUE if its VARIANT argument contains an OBJECT value.

Semi-structured Data Functions

IS_TIME

Verifies whether a VARIANT value contains a TIME value.

Semi-structured Data Functions

IS_TIMESTAMP_*

Verifies whether a VARIANT value contains the respective TIMESTAMP value.

Semi-structured Data Functions

L

LAG

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

Aggregate Functions , Window Functions

LAST_DAY

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

Date & Time Functions

LAST_QUERY_ID

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

Context Functions

LAST_TRANSACTION

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

Context Functions

LAST_VALUE

Returns the last value within an ordered group of values.

Aggregate Functions , Window Functions

LEAD

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

Aggregate Functions , Window Functions

LEAST

Returns the smallest value from a list of expressions.

Conditional Expression Functions

LEFT

Returns a leftmost substring of its input.

String & Binary Functions

LENGTH

Returns the length of a input string or binary value.

String & Binary Functions

LIKE

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

String & Binary Functions

LIKE ANY

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

String & Binary Functions

LISTAGG

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

Aggregate Functions , Window Functions

LN

Returns the natural logarithm of a numeric expression.

Numeric Functions

LOCALTIME

Returns the current time for the system.

Context Functions

LOCALTIMESTAMP

Returns the current timestamp for the system.

Context Functions

LOG

Returns the logarithm of a numeric expression.

Numeric Functions

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

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

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 — Deprecated

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

String & Binary Functions

MEDIAN

Determines the median of a set of values.

Aggregate Functions , Window Functions

MIN / MAX

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

Aggregate Functions , Window 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 Functions

MINHASH_COMBINE

Combines input MinHash states into a single MinHash output state.

Aggregate Functions , Window Functions

MOD

Returns the remainder of input expr1 divided by input expr2.

Numeric Functions

MONTHNAME

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

Date & Time Functions

N

NEXT_DAY

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

Date & Time Functions

NORMAL

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

Data Generation Functions

NTH_VALUE

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

Aggregate Functions , Window Functions

NTILE

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

Aggregate Functions , Window Functions

NULLIF

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

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 Data Functions

OBJECT_CONSTRUCT

Returns an object constructed from the arguments.

Semi-structured Data Functions

OBJECT_DELETE

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

Semi-structured Data Functions

OBJECT_INSERT

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

Semi-structured Data Functions

OCTET_LENGTH

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

String & Binary Functions

P

PARSE_IP

Returns a JSON object consisting of all the components from a valid INET (Internet Protocol) or CIDR (Classless Internet Domain Routing) IPv4 or IPv6 string.

String & Binary Functions

PARSE_JSON

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

Semi-structured Data Functions

PARSE_URL

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

String & Binary Functions

PARSE_XML

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

Semi-structured Data Functions

PERCENT_RANK

Returns the relative rank of a value within a group of values.

Aggregate Functions , 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

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

Aggregate Functions , Window Functions

RATIO_TO_REPORT

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

Window Functions

REGEXP

Returns true if the subject matches the specified pattern.

String Functions (Regular Expressions)

REGEXP_COUNT

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

String Functions (Regular Expressions)

REGEXP_INSTR

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

String Functions (Regular Expressions)

REGEXP_LIKE

Returns true if the subject matches the 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)

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 Functions

REGR_INTERCEPT

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

Aggregate Functions , Window Functions

REGR_R2

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

Aggregate Functions , Window Functions

REGR_SLOPE

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

Aggregate Functions , Window Functions

REGR_SXX

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

Aggregate Functions , Window Functions

REGR_SXY

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

Aggregate Functions , Window Functions

REGR_SYY

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

Aggregate Functions , Window Functions

REGR_VALX

If the first argument is NULL, returns NULL.

Conditional Expression Functions

REGR_VALY

If the second argument is NULL, returns NULL; otherwise, returns the first argument.

Conditional Expression Functions

REPEAT

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

String & Binary Functions

REPLACE

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

String & Binary Functions

RESULT_SCAN

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

Table Functions

REVERSE

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

String & Binary Functions

RIGHT

Returns a rightmost substring of its input.

String & Binary Functions

RLIKE

Returns true if the subject matches the specified pattern.

String Functions (Regular Expressions)

ROUND

Returns rounded values for input_expr.

Numeric Functions

ROW_NUMBER

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

Aggregate Functions , Window Functions

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

SEQ1 / SEQ2 / SEQ4 / SEQ8

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

Data Generation 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

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

SOUNDEX

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

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

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

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

Aggregate Functions , Window Functions

STDDEV_POP

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

Aggregate Functions , Window Functions

STDDEV_SAMP

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

Aggregate Functions , Window Functions

STRIP_NULL_VALUE

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

Semi-structured Data Functions

STRTOK

Tokenizes a given string and returns the requested part.

String & Binary Functions

STRTOK_SPLIT_TO_TABLE

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

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

SYSTEM$ABORT_SESSION

Aborts the specified session.

System Functions

SYSTEM$ABORT_TRANSACTION

Aborts the specified transaction, if it is running.

System Functions

SYSTEM$CANCEL_ALL_QUERIES

Cancels all active/running queries in the specified session.

System Functions

SYSTEM$CANCEL_QUERY

Cancels the specified query (or statement) if it is currently active/running.

System Functions

SYSTEM$CLUSTERING_DEPTH

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

System Functions

SYSTEM$CLUSTERING_INFORMATION

Returns clustering information, including average clustering depth, for a table based on one or more columns in the table.

System Functions

SYSTEM$CLUSTERING_RATIO — Deprecated

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

System Functions

SYSTEM$CURRENT_USER_TASK_NAME

Returns the name of the task currently executing when invoked from the statement or stored procedure defined by the task.

System Functions

SYSTEM$DATABASE_REFRESH_HISTORY

Returns the refresh history for a secondary database (for database replication).

System Functions

SYSTEM$DATABASE_REFRESH_PROGRESS , SYSTEM$DATABASE_REFRESH_PROGRESS_BY_JOB

Returns the status of a database refresh along various dimensions (for database replication).

System Functions

SYSTEM$GET_AWS_SNS_IAM_POLICY

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

System Functions

SYSTEM$GET_PREDECESSOR_RETURN_VALUE

Retrieves the return value for the predecessor task in a tree of tasks. The return value is explicitly set by calling the SYSTEM$SET_RETURN_VALUE function by the predecessor task.

System Functions

SYSTEM$LAST_CHANGE_COMMIT_TIME

Returns the commit time of the last DML change performed on a table or a view.

System Functions

SYSTEM$PIPE_FORCE_RESUME

Forces a pipe paused using ALTER PIPE to resume.

System Functions

SYSTEM$PIPE_STATUS

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

System Functions

SYSTEM$SET_RETURN_VALUE

Explicitly sets the return value for a task. A direct child task in a tree of tasks can call the SYSTEM$GET_PREDECESSOR_RETURN_VALUE function to retrieve this value.

System Functions

SYSTEM$SHOW_OAUTH_CLIENT_SECRETS

Returns the client secrets in a string.

System Functions

SYSTEM$STREAM_GET_TABLE_TIMESTAMP

Returns a timestamp indicating the transactional point when the stream contents were last consumed using a DML statement.

System Functions

SYSTEM$STREAM_HAS_DATA

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

System Functions

SYSTEM$TASK_DEPENDENTS_ENABLE

Recursively enables all dependent tasks tied to a specified root task.

System Functions

SYSTEM$TYPEOF

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

System Functions

SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS

Aborts the next run of the specified task if the system had already started to process it (i.e. its state is EXECUTING).

System Functions

SYSTEM$WAIT

Waits for the specified amount of time before proceeding.

System Functions

SYSTEM$WHITELIST

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

System Functions

SYSTEM$WHITELIST_PRIVATELINK

Returns hostnames and port numbers for AWS PrivateLink deployments to add to your firewall’s whitelist so that you can access Snowflake from behind your firewall.

System Functions

T

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 (i.e. parent) task in a simple tree of tasks.

Information Schema , Table Functions

TASK_HISTORY

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

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

Conversion Functions , Semi-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

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_JSON

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

Conversion Functions , Semi-structured Data Functions

TO_OBJECT

Converts the input value to an object.

Conversion Functions , Semi-structured Data Functions

TO_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 VARIANT value or NULL (if input is NULL).

Conversion Functions

TO_XML

Converts any VARIANT value to a string containing the XML representation of the value.

Conversion Functions , Semi-structured Data Functions

TRANSLATE

Translates subject from the characters in sourceAlphabet to the characters in targetAlphabet.

String & Binary Functions

TRIM

Removes leading and trailing characters from a string.

String & Binary Functions

TRUNCATE , TRUNC

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

Numeric Functions

TRUNC

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

Date & Time Functions

TRY_BASE64_DECODE_BINARY

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

String & Binary Functions

TRY_BASE64_DECODE_STRING

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

String & Binary Functions

TRY_CAST

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

Conversion Functions

TRY_HEX_DECODE_BINARY

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

String & Binary Functions

TRY_HEX_DECODE_STRING

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

String & Binary Functions

TRY_PARSE_JSON

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

Semi-structured Data Functions

TRY_TO_BINARY

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

Conversion Functions

TRY_TO_BOOLEAN

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

Conversion Functions

TRY_TO_DATE

A special version of TO_DATE 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

TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC

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

Conversion Functions

TRY_TO_DOUBLE

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

Conversion Functions

TRY_TO_TIME

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

Conversion Functions

TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*

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

Conversion Functions

TYPEOF

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

Semi-structured Data Functions

U

UNICODE

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

String & Binary Functions

UNIFORM

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

Data Generation Functions

UPPER

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

String & Binary Functions

UUID_STRING

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

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

VAR_POP

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

Aggregate Functions , Window Functions

VAR_SAMP

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

Aggregate Functions , Window Functions

VARIANCE , VARIANCE_SAMP

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

Aggregate Functions , Window Functions

VARIANCE_POP

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

Aggregate Functions , Window 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.

Window Functions

X

XMLGET

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

Semi-structured Data Functions

Y

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

Extracts the corresponding date part from a date or timestamp.

Date & Time Functions

Z

ZEROIFNULL

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

Conditional Expression Functions

ZIPF

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

Data Generation Functions