You are viewing documentation about an older version (1.4.0). View latest version

Functions¶

Functions

`abs`(e)	Returns the absolute value of a numeric expression.
`acos`(e)	Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval [-pi, pi].
`add_months`(date_or_timestamp, number_of_months)	Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information.
`any_value`(e)	Returns a non-deterministic any value for the specified column.
`approx_count_distinct`(e)	Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e.
`approx_percentile`(col, percentile)	Returns an approximated value for the desired percentile.
`approx_percentile_accumulate`(col)	Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation.
`approx_percentile_combine`(state)	Combines (merges) percentile input states into a single output state.
`approx_percentile_estimate`(state, percentile)	Returns the desired approximated percentile value for the specified t-Digest state.
`array_agg`(col[, is_distinct])	Returns the input values, pivoted into an ARRAY.
`array_append`(array, element)	Returns an ARRAY containing all elements from the source ARRAY as well as the new element.
`array_cat`(array1, array2)	Returns the concatenation of two ARRAYs.
`array_compact`(array)	Returns a compacted ARRAY with missing and null values removed, effectively converting sparse arrays into dense arrays.
`array_construct`(*cols)	Returns an ARRAY constructed from zero, one, or more inputs.
`array_construct_compact`(*cols)	Returns an ARRAY constructed from zero, one, or more inputs.
`array_contains`(variant, array)	Returns True if the specified VARIANT is found in the specified ARRAY.
`array_distinct`(col)	The function excludes any duplicate elements that are present in the input ARRAY.
`array_insert`(array, pos, element)	Returns an ARRAY containing all elements from the source ARRAY as well as the new element.
`array_intersection`(array1, array2)	Returns an array that contains the matching elements in the two input arrays.
`array_position`(variant, array)	Returns the index of the first occurrence of an element in an ARRAY.
`array_prepend`(array, element)	Returns an ARRAY containing the new element as well as all elements from the source ARRAY.
`array_size`(array)	Returns the size of the input ARRAY.
`array_slice`(array, from_, to)	Returns an ARRAY constructed from a specified subset of elements of the input ARRAY.
`array_to_string`(array, separator)	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).
`arrays_overlap`(array1, array2)	Compares whether two ARRAYs have at least one element in common.
`as_array`(variant)	Casts a VARIANT value to an array.
`as_binary`(variant)	Casts a VARIANT value to a binary string.
`as_char`(variant)	Casts a VARIANT value to a string.
`as_date`(variant)	Casts a VARIANT value to a date.
`as_decimal`(variant[, precision, scale])	Casts a VARIANT value to a fixed-point decimal (does not match floating-point values).
`as_double`(variant)	Casts a VARIANT value to a floating-point value.
`as_integer`(variant)	Casts a VARIANT value to an integer.
`as_number`(variant[, precision, scale])	Casts a VARIANT value to a fixed-point decimal (does not match floating-point values).
`as_object`(variant)	Casts a VARIANT value to an object.
`as_real`(variant)	Casts a VARIANT value to a floating-point value.
`as_time`(variant)	Casts a VARIANT value to a time value.
`as_timestamp_ltz`(variant)	Casts a VARIANT value to a TIMESTAMP with a local timezone.
`as_timestamp_ntz`(variant)	Casts a VARIANT value to a TIMESTAMP with no timezone.
`as_timestamp_tz`(variant)	Casts a VARIANT value to a TIMESTAMP with a timezone.
`as_varchar`(variant)	Casts a VARIANT value to a string.
`asc`(c)	Returns a Column expression with values sorted in ascending order.
`asc_nulls_first`(c)	Returns a Column expression with values sorted in ascending order (null values sorted before non-null values).
`asc_nulls_last`(c)	Returns a Column expression with values sorted in ascending order (null values sorted after non-null values).
`ascii`(e)	Returns the ASCII code for the first character of a string.
`asin`(e)	Computes the inverse sine (arc sine) of its input; the result is a number in the interval [-pi, pi].
`atan`(e)	Computes the inverse tangent (arc tangent) of its input; the result is a number in the interval [-pi, pi].
`atan2`(y, x)	Computes the inverse tangent (arc tangent) of its input; the result is a number in the interval [-pi, pi].
`avg`(e)	Returns the average of non-NULL records.
`bitnot`(e)	Returns the bitwise negation of a numeric expression.
`bitshiftleft`(to_shift_column, n)	Returns the bitwise negation of a numeric expression.
`bitshiftright`(to_shift_column, n)	Returns the bitwise negation of a numeric expression.
`builtin`(function_name)	Function object to invoke a Snowflake system-defined function (built-in function).
`bround`(col, scale)	Rounds the number using HALF_TO_EVEN option.
`call_builtin`(function_name, *args)	Invokes a Snowflake system-defined function (built-in function) with the specified name and arguments.
`call_function`(function_name, *args)	Invokes a Snowflake system-defined function (built-in function) with the specified name and arguments.
`call_table_function`(function_name, *args, ...)	Invokes a Snowflake table function, including system-defined table functions and user-defined table functions.
`call_udf`(udf_name, *args)	Calls a user-defined function (UDF) by name.
`cast`(column, to)	Converts a value of one data type into another data type.
`ceil`(e)	Returns values from the specified column rounded to the nearest equal or larger integer.
`char`(col)	Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode.
`charindex`(target_expr, source_expr[, position])	Searches for `target_expr` in `source_expr` and, if successful, returns the position (1-based) of the `target_expr` in `source_expr`.
`check_json`(col)	Checks the validity of a JSON document.
`check_xml`(col)	Checks the validity of an XML document.
`coalesce`(*e)	Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
`col`(col_name)	Returns the `Column` with the specified name.
`collate`(e, collation_spec)	Returns a copy of the original `Column` with the specified `collation_spec` property, rather than the original collation specification property.
`collation`(e)	Returns the collation specification of expr.
`column`(col_name)	Returns a `Column` with the specified name.
`concat`(*cols)	Concatenates one or more strings, or concatenates one or more binary values.
`concat_ws`(*cols)	Concatenates two or more strings, or concatenates two or more binary values.
`contains`(col, string)	Returns if col contains string for each row.
`convert_timezone`(target_timezone, source_time)	Converts the given source_time to the target timezone.
`corr`(column1, column2)	Returns the correlation coefficient for non-null pairs in a group.
`cos`(e)	Computes the cosine of its argument; the argument should be expressed in radians.
`cosh`(e)	Computes the hyperbolic cosine of its argument.
`count`(e)	Returns either the number of non-NULL records for the specified columns, or the total number of records.
`countDistinct`(*cols)	Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records.
`count_distinct`(*cols)	Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records.
`covar_pop`(column1, column2)	Returns the population covariance for non-null pairs in a group.
`covar_samp`(column1, column2)	Returns the sample covariance for non-null pairs in a group.
`cume_dist`()	Finds the cumulative distribution of a value with regard to other values within the same window partition.
`current_available_roles`()	Returns a JSON string that lists all roles granted to the current user.
`current_database`()	Returns the name of the database in use for the current session.
`current_date`()	Returns the current date for the system.
`current_region`()	Returns the name of the region for the account where the current user is logged in.
`current_role`()	Returns the name of the role in use for the current session.
`current_schema`()	Returns the name of the schema in use for the current session.
`current_schemas`()	Returns active search path schemas.
`current_session`()	Returns a unique system identifier for the Snowflake session corresponding to the present connection.
`current_statement`()	Returns the SQL text of the statement that is currently executing.
`current_time`()	Returns the current time for the system.
`current_timestamp`()	Returns the current timestamp for the system.
`current_user`()	Returns the name of the user currently logged into the system.
`current_version`()	Returns the current Snowflake version.
`current_warehouse`()	Returns the name of the warehouse in use for the current session.
`date_format`(e[, fmt])	Converts an input expression into a date.
`date_from_parts`(y, m, d)	Creates a date from individual numeric components that represent the year, month, and day of the month.
`date_part`(part, e)	Extracts the specified date or time part from a date, time, or timestamp.
`date_trunc`(part, expr)	Truncates a DATE, TIME, or TIMESTAMP to the specified precision.
`dateadd`(part, col1, col2)	Adds the specified value for the specified date or time part to date or time expr.
`datediff`(part, col1, col2)	Calculates the difference between two date, time, or timestamp columns based on the date or time part requested.
`date_add`(col, num_of_days)	Adds a number of days to a date column.
`date_sub`(col, num_of_days)	Subtracts a number of days from a date column.
`daydiff`(col1, col2)	Calculates the difference between two dates, or timestamp columns based in days.
`dayname`(e)	Extracts the three-letter day-of-week name from the specified date or timestamp.
`dayofmonth`(e)	Extracts the corresponding day (number) of the month from a date or timestamp.
`dayofweek`(e)	Extracts the corresponding day (number) of the week from a date or timestamp.
`dayofyear`(e)	Extracts the corresponding day (number) of the year from a date or timestamp.
`degrees`(e)	Converts radians to degrees.
`dense_rank`()	Returns the rank of a value within a group of values, without gaps in the ranks.
`desc`(c)	Returns a Column expression with values sorted in descending order.
`desc_nulls_first`(c)	Returns a Column expression with values sorted in descending order (null values sorted before non-null values).
`desc_nulls_last`(c)	Returns a Column expression with values sorted in descending order (null values sorted after non-null values).
`div0`(dividend, divisor)	Performs division like the division operator (/), but returns 0 when the divisor is 0 (rather than reporting an error).
`endswith`(col, str)	Returns true if col ends with str.
`equal_nan`(e)	Return true if the value in the column is not a number (NaN).
`exp`(e)	Computes Euler's number e raised to a floating-point value.
`explode`(col)	Flattens a given array or map type column into individual rows.
`expr`(sql)	Creates a `Column` expression from raw SQL text.
`factorial`(e)	Computes the factorial of its input.
`first_value`(e[, ignore_nulls])	Returns the first value within an ordered group of values.
`floor`(e)	Returns values from the specified column rounded to the nearest equal or smaller integer.
`from_unixtime`(e[, fmt])	Converts an input expression into the corresponding timestamp.
`function`(function_name)	Function object to invoke a Snowflake system-defined function (built-in function).
`get`(col1, col2)	Extracts a value from an object or array; returns NULL if either of the arguments is NULL.
`get_ignore_case`(obj, field)	Extracts a field value from an object.
`get_path`(col, path)	Extracts a value from semi-structured data using a path name.
`greatest`(*columns)	Returns the largest value from a list of expressions.
`grouping`(*cols)	Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
`grouping_id`(*cols)	Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
`hash`(*cols)	Returns a signed 64-bit hash value.
`hour`(e)	Extracts the hour from a date or timestamp.
`iff`(condition, expr1, expr2)	Returns one of two specified expressions, depending on a condition.
`in_`(cols, *vals)	Returns a conditional expression that you can pass to the filter or where methods to perform the equivalent of a WHERE .
`initcap`(e[, delimiters])	Returns the input string with the first letter of each word in uppercase and the subsequent letters in lowercase.
`insert`(base_expr, position, length, insert_expr)	Replaces a substring of the specified length, starting at the specified position, with a new string or binary value.
`is_array`(col)	Returns true if the specified VARIANT column contains an ARRAY value.
`is_binary`(col)	Returns true if the specified VARIANT column contains a binary value.
`is_boolean`(col)	Returns true if the specified VARIANT column contains a boolean value.
`is_char`(col)	Returns true if the specified VARIANT column contains a string.
`is_date`(col)	Returns true if the specified VARIANT column contains a date value.
`is_date_value`(col)	Returns true if the specified VARIANT column contains a date value.
`is_decimal`(col)	Returns true if the specified VARIANT column contains a fixed-point decimal value or integer.
`is_double`(col)	Returns true if the specified VARIANT column contains a floating-point value, fixed-point decimal, or integer.
`is_integer`(col)	Returns true if the specified VARIANT column contains a integer value.
`is_null`(e)	Return true if the value in the column is null.
`is_null_value`(col)	Returns true if the specified VARIANT column contains a JSON null value.
`is_object`(col)	Returns true if the specified VARIANT column contains an OBJECT value.
`is_real`(col)	Returns true if the specified VARIANT column contains a floating-point value, fixed-point decimal, or integer.
`is_time`(col)	Returns true if the specified VARIANT column contains a TIME value.
`is_timestamp_ltz`(col)	Returns true if the specified VARIANT column contains a TIMESTAMP_LTZ value to be interpreted using the local time zone.
`is_timestamp_ntz`(col)	Returns true if the specified VARIANT column contains a TIMESTAMP_NTZ value with no time zone.
`is_timestamp_tz`(col)	Returns true if the specified VARIANT column contains a TIMESTAMP_TZ value with a time zone.
`is_varchar`(col)	Returns true if the specified VARIANT column contains a string.
`json_extract_path_text`(col, path)	Parses a JSON string and returns the value of an element at a specified path in the resulting JSON document.
`kurtosis`(e)	Returns the population excess kurtosis of non-NULL records.
`lag`(e[, offset, default_value, ignore_nulls])	Accesses data in a previous row in the same result set without having to join the table to itself.
`last_day`(e)	Returns the last day of the specified date part for a date or timestamp.
`last_value`(e[, ignore_nulls])	Returns the last value within an ordered group of values.
`lead`(e[, offset, default_value, ignore_nulls])	Accesses data in a subsequent row in the same result set without having to join the table to itself.
`least`(*columns)	Returns the smallest value from a list of expressions.
`left`(str_expr, length)	Returns a left most substring of `str_expr`.
`length`(e)	Returns the length of an input string or binary value.
`listagg`(e[, delimiter, is_distinct])	Returns the concatenated input values, separated by delimiter string.
`lit`(literal)	Creates a `Column` expression for a literal value.
`log`(base, x)	Returns the logarithm of a numeric expression.
`lower`(e)	Returns the input string with all characters converted to lowercase.
`lpad`(e, len, pad)	Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
`ltrim`(e[, trim_string])	Removes leading characters, including whitespace, from a string.
`max`(e)	Returns the maximum value for the records in a group.
`md5`(e)	Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
`mean`(e)	Return the average for the specific numeric columns.
`median`(e)	Returns the median value for the records in a group.
`min`(e)	Returns the minimum value for the records in a group.
`minute`(e)	Extracts the minute from a date or timestamp.
`mode`(e)	Returns the most frequent value for the records in a group.
`monotonically_increasing_id`([sign])	Returns a sequence of monotonically increasing integers, with wrap-around which happens after largest representable integer of integer width 8 byte.
`month`(e)	Extracts the month from a date or timestamp.
`monthname`(e)	Extracts the three-letter month name from the specified date or timestamp.
`months_between`(date1, date2)	Returns the number of months between two DATE or TIMESTAMP values.
`negate`(e)	Returns the negation of the value in the column (equivalent to a unary minus).
`next_day`(date, day_of_week)	Returns the date of the first specified DOW (day of week) that occurs after the input date.
`not_`(e)	Returns the inverse of a boolean expression.
`ntile`(e)	Divides an ordered data set equally into the number of buckets specified by n.
`object_agg`(key, value)	Returns one OBJECT per group.
`object_construct`(*key_values)	Returns an OBJECT constructed from the arguments.
`object_construct_keep_null`(*key_values)	Returns an object containing the contents of the input (i.e.
`object_delete`(obj, key1, *keys)	Returns an object consisting of the input object with one or more keys removed.
`object_insert`(obj, key, value[, update_flag])	Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).
`object_keys`(obj)	Returns an array containing the list of keys in the input object.
`object_pick`(obj, key1, *keys)	Returns a new OBJECT containing some of the key-value pairs from an existing object.
`pandas_udf`([func, return_type, input_types, ...])	Registers a Python function as a vectorized UDF and returns the UDF.
`parse_json`(e)	Parse the value of the specified column as a JSON string and returns the resulting JSON document.
`parse_xml`(e)	Parse the value of the specified column as a JSON string and returns the resulting XML document.
`percent_rank`()	Returns the relative rank of a value within a group of values, specified as a percentage ranging from 0.0 to 1.0.
`percentile_cont`(percentile)	Return a percentile value based on a continuous distribution of the input column.
`pow`(left, right)	Returns a number (left) raised to the specified power (right).
`previous_day`(date, day_of_week)	Returns the date of the first specified DOW (day of week) that occurs before the input date.
`quarter`(e)	Extracts the quarter from a date or timestamp.
`radians`(e)	Converts degrees to radians.
`random`([seed])	Each call returns a pseudo-random 64-bit integer.
`rank`()	Returns the rank of a value within an ordered group of values.
`regexp_count`(subject, pattern[, position])	Returns the number of times that a pattern occurs in the subject.
`regexp_extract`(value, regexp, idx)	Extract a specific group matched by a regex, from the specified string column.
`regexp_replace`(subject, pattern[, ...])	Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string.
`repeat`(s, n)	Builds a string by repeating the input for the specified number of times.
`replace`(subject, pattern[, replacement])	Removes all occurrences of a specified subject and optionally replaces them with replacement.
`right`(str_expr, length)	Returns a right most substring of `str_expr`.
`round`(e[, scale])	Returns rounded values from the specified column.
`row_number`()	Returns a unique row number for each row within a window partition.
`rpad`(e, len, pad)	Right-pads a string with characters from another string, or right-pads a binary value with bytes from another binary value.
`rtrim`(e[, trim_string])	Removes trailing characters, including whitespace, from a string.
`second`(e)	Extracts the second from a date or timestamp.
`seq1`([sign])	Returns a sequence of monotonically increasing integers, with wrap-around which happens after largest representable integer of integer width 1 byte.
`seq2`([sign])	Returns a sequence of monotonically increasing integers, with wrap-around which happens after largest representable integer of integer width 2 byte.
`seq4`([sign])	Returns a sequence of monotonically increasing integers, with wrap-around which happens after largest representable integer of integer width 4 byte.
`seq8`([sign])	Returns a sequence of monotonically increasing integers, with wrap-around which happens after largest representable integer of integer width 8 byte.
`sha1`(e)	Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
`sha2`(e, num_bits)	Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
`sin`(e)	Computes the sine of its argument; the argument should be expressed in radians.
`sinh`(e)	Computes the hyperbolic sine of its argument.
`skew`(e)	Returns the sample skewness of non-NULL records.
`soundex`(e)	Returns a string that contains a phonetic representation of the input string.
`split`(str, pattern)	Splits a given string with a given separator and returns the result in an array of strings.
`sproc`([func, return_type, input_types, ...])	Registers a Python function as a Snowflake Python stored procedure and returns the stored procedure.
`sql_expr`(sql)	Creates a `Column` expression from raw SQL text.
`sqrt`(e)	Returns the square-root of a non-negative numeric expression.
`startswith`(col, str)	Returns true if col starts with str.
`stddev`(e)	Returns the sample standard deviation (square root of sample variance) of non-NULL values.
`stddev_pop`(e)	Returns the population standard deviation (square root of variance) of non-NULL values.
`stddev_samp`(e)	Returns the sample standard deviation (square root of sample variance) of non-NULL values.
`strip_null_value`(col)	Converts a JSON "null" value in the specified column to a SQL NULL value.
`strtok_to_array`(text[, delimiter])	Tokenizes the given string using the given set of delimiters and returns the tokens as an array.
`struct`(*cols)	Returns an OBJECT constructed with the given columns.
`substr`(str, pos, len)	Returns the portion of the string or binary value str, starting from the character/byte specified by pos, with limited length.
`substring`(str, pos, len)	Returns the portion of the string or binary value str, starting from the character/byte specified by pos, with limited length.
`sum`(e)	Returns the sum of non-NULL records in a group.
`sum_distinct`(e)	Returns the sum of non-NULL distinct records in a group.
`sysdate`()	Returns the current timestamp for the system, but in the UTC time zone.
`table_function`(function_name)	Create a function object to invoke a Snowflake table function.
`tan`(e)	Computes the tangent of its argument; the argument should be expressed in radians.
`tanh`(e)	Computes the hyperbolic tangent of its argument.
`time_from_parts`(hour, minute, second[, ...])	Creates a time from individual numeric components.
`timestamp_from_parts`()	Creates a timestamp from individual numeric components.
`timestamp_ltz_from_parts`(year, month, day, ...)	Creates a timestamp from individual numeric components.
`timestamp_ntz_from_parts`()	Creates a timestamp from individual numeric components.
`timestamp_tz_from_parts`(year, month, day, ...)	Creates a timestamp from individual numeric components and a string timezone.
`to_array`(e)	Converts any value to an ARRAY value or NULL (if input is NULL).
`to_binary`(e[, fmt])	Converts the input expression to a binary value.
`to_char`(c[, format])	Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode.
`to_date`(e[, fmt])	Converts an input expression into a date.
`to_decimal`(e, precision, scale)	Converts an input expression to a decimal.
`to_geography`(e)	Parses an input and returns a value of type GEOGRAPHY.
`to_json`(e)	Converts any VARIANT value to a string containing the JSON representation of the value.
`to_object`(e)	Converts any value to a OBJECT value or NULL (if input is NULL).
`to_time`(e[, fmt])	Converts an input expression into the corresponding time.
`to_timestamp`(e[, fmt])	Converts an input expression into the corresponding timestamp.
`to_varchar`(c[, format])	Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode.
`to_variant`(e)	Converts any value to a VARIANT value or NULL (if input is NULL).
`to_xml`(e)	Converts any VARIANT value to a string containing the XML representation of the value.
`translate`(src, source_alphabet, target_alphabet)	Translates src from the characters in source_alphabet to the characters in target_alphabet.
`trim`(e[, trim_string])	Removes leading and trailing characters from a string.
`trunc`(e[, scale])	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.
`try_cast`(column, to)	A special version of CAST for a subset of data type conversions.
`typeof`(col)	Reports the type of a value stored in a VARIANT column.
`udf`([func, return_type, input_types, name, ...])	Registers a Python function as a Snowflake Python UDF and returns the UDF.
`udtf`([handler, input_types, name, ...])	Registers a Python class as a Snowflake Python UDTF and returns the UDTF.
`uniform`(min_, max_, gen)	Returns a uniformly random number.
`unix_timestamp`(e[, fmt])	Converts a timestamp or a timestamp string to Unix time stamp (in seconds).
`upper`(e)	Returns the input string with all characters converted to uppercase.
`var_pop`(e)	Returns the population variance of non-NULL records in a group.
`var_samp`(e)	Returns the sample variance of non-NULL records in a group.
`variance`(e)	Returns the sample variance of non-NULL records in a group.
`weekofyear`(e)	Extracts the corresponding week (number) of the year from a date or timestamp.
`when`(condition, value)	Works like a cascading if-then-else statement.
`when_matched`([condition])	Specifies a matched clause for the `Table.merge` action.
`when_not_matched`([condition])	Specifies a not-matched clause for the `Table.merge` action.
`xmlget`(xml, tag[, instance_num])	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).
`year`(e)	Extracts the year from a date or timestamp.