Functions (Alphabetical)

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

Function Name Summary Category
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
APPROX_COUNT_DISTINCT Uses HyperLogLog to return an approximation of the distinct cardinality of the input. Aggregate 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
APPROX_PERCENTILE_ACCUMULATE Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. Aggregate Functions
APPROX_PERCENTILE_COMBINE Combines (merges) percentile input states into a single output state. Aggregate Functions
APPROX_PERCENTILE_ESTIMATE Returns the desired approximated percentile value for the specified t-Digest state. Aggregate 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
APPROX_TOP_K_ACCUMULATE Returns the Space-Saving summary at the end of aggregation. Aggregate Functions
APPROX_TOP_K_COMBINE Combines (merges) input states into a single output state. Aggregate Functions
APPROX_TOP_K_ESTIMATE Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. Aggregate Functions
APPROXIMATE_JACCARD_INDEX Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. Aggregate Functions
APPROXIMATE_SIMILARITY Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. Aggregate Functions
ARRAY_AGG Returns the input values, pivoted into an ARRAY. Aggregate 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. 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 first occurring index 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 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
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 , 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 , 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 , 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
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. 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
CONCAT , || Concatenates two strings or two 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
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 expr or a total number of records. Aggregate Functions , Analytic / Window Functions
COVAR_POP Returns the population covariance for non-null pairs in a group. Aggregate Functions
COVAR_SAMP Returns the sample covariance for non-null pairs in a group. Aggregate Functions
CUME_DIST Finds the cumulative distribution of a value with regard to other values within the same window partition. Analytic / Window 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_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
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_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. 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 part. 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
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. Analytic / Window Functions
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
EXTRACT Extracts the specified date or time part from a date, time, or timestamp. Date & Time Functions
FACTORIAL Computes the factorial of its input. Numeric Functions
FIRST_VALUE Returns the first value within an ordered group of values. Analytic / 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. Numeric Functions
GENERATOR Creates rows of data based either on a specified number of rows, a specified generation period (in seconds), or both. Table Functions
GET Extracts a value from an object or array; 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. Miscellaneous 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
HASH Returns a signed 64-bit hash value. Miscellaneous Functions
HASH_AGG Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. Aggregate Functions , Miscellaneous 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
HLL_ACCUMULATE Returns the HyperLogLog state at the end of aggregation. Aggregate Functions
HLL_COMBINE Combines (merges) input states into a single output state. Aggregate Functions
HLL_ESTIMATE Returns the cardinality estimate for the given HyperLogLog state. Aggregate Functions
HLL_EXPORT Converts input in BINARY format to OBJECT format. Aggregate Functions
HLL_IMPORT Converts input in OBJECT format to BINARY format. Aggregate Functions
HOUR / MINUTE / SECOND Extracts the corresponding time part from a time or timestamp value. Date & Time Functions
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 at the specified position and length 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
LAG Accesses data in a previous row in the same result set without having to join the table to itself. Analytic / 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. Analytic / Window Functions
LEAD Accesses data in a subsequent row in the same result set without having to join the table to itself. Analytic / 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
LISTAGG Returns the concatenated input values, separated by the delimiter string. Aggregate 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 base (base) of a numeric expression (expr). 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
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 containing the 128-bit MD5 message digest. String & Binary Functions
MD5_NUMBER 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
MIN / MAX Returns the minimum or maximum value for the records within expr. Aggregate Functions , Analytic / 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
MINHASH_COMBINE Combines input MinHash states into a single MinHash output state. Aggregate 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
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. Miscellaneous Functions
NTH_VALUE Returns the nth value (up to 1000) within an ordered group of values. Analytic / Window Functions
NTILE Divides an ordered data set equally into the number of buckets specified by constant_value. Analytic / 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 the nullness of the first argument. Conditional Expression Functions
OBJECT_AGG Returns one OBJECT per group. Aggregate 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
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. Analytic / Window Functions
PERCENTILE_CONT Return a percentile value based on a continuous distribution of the input column (specified in order_by_expr). Aggregate Functions
PERCENTILE_DISC Returns a percentile value based on a discrete distribution of the input column (specified in order_by_expr). Aggregate 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 an 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
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
RADIANS Converts degrees to radians. Numeric Functions
RANDOM Each call returns a pseudo-random 64-bit integer. Miscellaneous Functions
RANDSTR Returns a random string of specified length. Miscellaneous Functions
RANK Returns the rank of a value within an ordered group of values. Analytic / 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
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
REGR_COUNT Returns the number of non-null number pairs in a group. Aggregate Functions
REGR_INTERCEPT Returns the intercept of the univariate linear regression line for non-null pairs in a group. Aggregate Functions
REGR_R2 Returns the coefficient of determination for non-null pairs in a group. Aggregate Functions
REGR_SLOPE Returns the slope of the linear regression line for non-null pairs in a group. Aggregate Functions
REGR_SXX Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs. Aggregate Functions
REGR_SXY Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs. Aggregate Functions
REGR_SYY Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs. Aggregate Functions
REGR_VALX Returns its first argument, but only if the second argument is not NULL; otherwise returns NULL. Conditional Expression Functions
REGR_VALY Returns its second argument, but only if the first argument is not NULL; otherwise returns NULL. 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. Analytic / Window Functions
RPAD Right-pads a string or binary value with characters from another string. 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
SEQ1 / SEQ2 / SEQ4 / SEQ8 Returns a sequence of monotonically increasing integers, with wrap-around. Miscellaneous 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
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
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
STDDEV_POP Returns the population standard deviation (square root of variance) of non-NULL values. Aggregate Functions
STDDEV_SAMP Returns the sample standard deviation (square root of sample variance) of non-NULL values. Aggregate Functions
STRIP_NULL_VALUE Converts a JSON “null” value to a SQL NULL value. 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 , Analytic / Window Functions
SYSTEM$ABORT_SESSION Aborts the specified session. Miscellaneous Functions
SYSTEM$ABORT_TRANSACTION Aborts the specified transaction, if it is running. Miscellaneous Functions
SYSTEM$CANCEL_ALL_QUERIES Cancels all active/running queries in the specified session. Miscellaneous Functions
SYSTEM$CANCEL_QUERY Cancels the specified query (or statement) if it is currently active/running. Miscellaneous Functions
SYSTEM$CLUSTERING_DEPTH Computes the average depth of the table according to the specified columns (or clustering keys defined for the table). Miscellaneous Functions
SYSTEM$CLUSTERING_INFORMATION Returns clustering information for a table based on one or more columns in the table. Miscellaneous Functions
SYSTEM$CLUSTERING_RATIO Calculates the clustering ratio for a table, based on one or more columns in the table. Miscellaneous Functions
SYSTEM$LAST_CHANGE_COMMIT_TIME Returns the commit time of the last DML change performed on a table or a view. Miscellaneous Functions
SYSTEM$PIPE_FORCE_RESUME Forces a pipe paused using ALTER PIPE to resume. Miscellaneous Functions
SYSTEM$PIPE_STATUS Retrieves a JSON representation of the current status of a pipe. Miscellaneous Functions
SYSTEM$TYPEOF Returns a string representing the SQL data type associated with an expression. Miscellaneous Functions
SYSTEM$WAIT Waits for a specified amount of time before proceeding. Miscellaneous Functions
TAN Computes the tangent of its argument; the argument should be expressed in radians. Numeric Functions
TANH Computes the hyperbolic tangent of its argument. Numeric Functions
TIME_FROM_PARTS Creates a time from individual numeric components. Date & Time Functions
TIMEADD Adds 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 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 towards zero. 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_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
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]. Miscellaneous 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 , Miscellaneous Functions
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
VAR_SAMP Returns the sample variance of non-NULL records in a group. Aggregate Functions
VARIANCE , VARIANCE_SAMP Returns the sample variance of non-NULL records in a group. Aggregate Functions
VARIANCE_POP Returns the population variance of non-NULL records in a group. Aggregate Functions
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. Analytic / Window Functions
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
YEAR* / DAY* / WEEK* / MONTH / QUARTER Extracts the corresponding date part from a date or timestamp. Date & Time Functions
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. Miscellaneous Functions