SQL Functions

All Functions

Function Name	Description
ABS	Computes the absolute value of a numeric expression.
ACOS	Computes the arccosine (inverse cosine) of a value in radians.
AES_DECRYPT	Decrypts a string produced by AES encryption.
AES_ENCRYPT	Encrypts a string using AES encryption.
APPROX_COUNT_DISTINCT	Returns the approximate number of unique, non-null values in a column.
ARRAY_CONTAINS	Returns whether a list contains a given value.
ASCII	Returns the ASCII code for the first character of a string. If the string is empty, 0 is returned.
ASIN	Computes the arcsine (inverse sine) of a value in radians.
ATAN	Computes the Arctangent (inverse Tangent) of a value.
AVG	Computes the average of a set of values.
BASE64	Returns the Base64 encoding of a binary string.
BINARY_STRING	Converts the input expression to a binary value.
BIT_AND	Returns the bitwise AND of non-NULL input values.
BIT_LENGTH	Gets length of bits of the input expression
BIT_OR	Returns the bitwise OR of non-NULL input values.
BOOL_AND	Computes the boolean AND of two boolean expressions. Returns TRUE if both expressions evaluate to TRUE. Returns FALSE if one or both expression(s) evaluate(s) to FALSE.
BOOL_OR	Computes the boolean OR of two boolean expressions. Returns TRUE if one or both expressions evaluate to TRUE. Returns FALSE if both expressions evaluate to FALSE.
BTRIM	Trims leading and trailing characters from a string.
CASE	Evaluates a list of conditions and returns the first resulting true expression. If a true expression is not found, will return the ELSE statement, if present, or else will return NULL.
CAST	Converts a value of one data type to another data type. This function behaves similarly to the TO_<data_type> (i.e. TO_TIMESTAMP) functions.
CBRT	Computes the cube root of a numeric expression
CEILING	Returns the nearest equal or larger value of the input expression. Can also be called using CEIL().
CHARACTER_LENGTH	Returns the length of an input string.
CHAR_LENGTH	Returns the character length of the input string.
CHR	Converts a Unicode code point into the character that matches the input Unicode character. If an invalid code point is specified, an empty string is returned.
COALESCE	Evaluates the arguments in order and returns the value of the first expression that does not contain NULL.
COL_LIKE	Tests whether an expression column matches a pattern column. Comparisons are case-sensitive.
CONCAT	Concatenates two or more strings. NULL values are ignored.
CONCAT_WS	Concatenate with separator. Returns a string resulting from the joining of two or more string values in an end-to-end manner. Uses the first argument as the separator between each string.
CONVERT_FROM	Converts a binary string from the given data type to a Spice type.
CONVERT_REPLACEUTF8	Converts a binary string to a UTF-8 value and replaces all characters that cannot be converted to UTF-8 with the specified replacement character.
CONVERT_TIMEZONE	Convert timestamp to the specified timezone.
CONVERT_TO	Converts a value to a binary string of a supported data type.
CORR	Calculates the Pearson correlation coefficient of the values expression1 and expression2. The function name must be enclosed in double quotes (“CORR”).
COS	Computes the cosine of a value in radians
COSH	Computes the hyperbolic cosine of a value in radians.
COT	Computes the cotangent of a value in radians.
COUNT	Returns the total number of records for the specified expression.
COVAR_POP	Returns the population covariance for non-NULL pairs across all input values.
COVAR_SAMP	Returns the sample covariance for non-NULL pairs across all input values.
CUME_DIST	Returns the cumulative distribution of the current row with regard to other values within the same window partition.
CURRENT_DATE	Returns the current date of the system.
CURRENT_DATE_UTC	Returns the current date of the system based on the UTC timezone.
CURRENT_TIME	Returns the current time for the system.
CURRENT_TIMESTAMP	Returns the current timestamp for the system in UTC time only.
DATEDIFF	Compares two dates or timestamps and returns the difference in days.
DATE_ADD	Returns the sum of two expressions of time as another expression of time.
DATE_DIFF	Returns the difference between two expressions of time as another expression of time.
DATE_PART	Return subfields such as year or hour from date or timestamp values
DATE_SUB	Returns the difference of two expressions of time as another expression of time.
DATE_TRUNC	Truncates the date or timestamp to the indicated precision.
DAY	Returns the day of month of the date or timestamp.
DAYOFMONTH	Returns the day of month of the date or timestamp.
DAYOFWEEK	Returns the day of the week (from 1 to 7) of the date or timestamp.
DAYOFYEAR	Returns the day of the year (from 1 to 366) of the date or timestamp.
DEGREES	Converts radians to degrees.
DENSE_RANK	Returns the rank of the current row within its partition and ordering. Rows that are equal will have the same rank.
E	Returns Euler’s number, a constant approximately equal to 2.718281828459045.
ENDS_WITH	Returns whether a string ends with another string. The comparison is case-sensitive.
EXP	Calculates Euler’s number, e, raised to the power of the specified value.
EXTRACT	Extracts the specified date or time part from the date or timestamp.
FIRST_VALUE	Returns the first value within an ordered group of a result set.
FLATTEN	Explodes compound values into multiple rows. This function takes a LIST column and produces a lateral view (that is, an inline view that contains correlation referring to other tables that precede it in the FROM clause).
FLOOR	Returns the value from the specifed expression rounded to the nearest equal or smaller integer.
FROM_HEX	Returns a binary value for the given hexadecimal string
GREATEST	Returns the largest value from a list of expressions.
HASH	Returns a hash value of the arguments. HASH does not return NULL, even for NULL inputs.
HEX	Returns the hexadecimal encoding of an expression.
HLL	Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
HOUR	Extracts the hour number (from 0 to 23) for a given time or timestamp.
ILIKE	Tests whether an expression matches a pattern. The comparison is case-insensitive.
INITCAP	Returns the input string with the first letter of each word in uppercase and the subsequent letters in the word are in lowercase).
INSTR	Returns the position of the first occurrence of a string when it is contained in another string. If no such occurrence is found, a zero is returned. The comparison is case-sensitive.
IS [NOT] DISTINCT FROM	Compares two expressions to determine whether they have the same or different values. NULLs are considered as comparable values.
ISFALSE	Tests whether the input expression is false. If it is, returns a value of true.
IS [NOT] NULL	Determines if an expression is NULL or not NULL. Alias for the function ISNULL/ISNOTNULL.
ISNUMERIC	Determines whether an expression is a valid numeric type (DECIMAL, DOUBLE, INT, BIGINT, VARBINARY).
ISTRUE	Returns TRUE if the input expression evaluates to TRUE.
IS_BIGINT	Returns TRUE if the input expression is an big integer value.
IS_DATE	Returns TRUE if the input expression can be cast to a date.
IS_INT	Returns TRUE if the input expression is an integer value.
IS_MEMBER	Returns whether the current user is a member of the specified role.
IS_UTF8	Returns whether an expression is valid UTF-8
IS_VARCHAR	Returns TRUE if the input expression is a varchar value.
LAG	Returns the row before the current one in a partition based on the ORDER BY clause without the need for a self-join. If there are no rows, this function returns NULL.
LAST_DAY	Returns the last day of the month for the specified date or timestamp.
LCASE	Returns the input expression with all the characters converted to lowercase.
LEAD	Returns the row after the current one in the same result set without the need for a self-join. If there are no rows, this function returns NULL.
LEAST	Returns the smallest value from a list of expressions.
LEFT	Returns the left-most substring. The function name must be enclosed in double quotes ("LEFT").
LENGTH	Returns the length of an input string. If the character encoding isn’t specified, it assumes to UTF8.
LEVENSHTEIN	Computes the Levenshtein distance between two input expressions.
LIKE	Tests whether an expression matches one or more patterns. Comparisons are case-sensitive.
LISTAGG	Concatenates a group of rows into a list of strings and places a separator between them.
LOCATE	Searches for the first occurrence of the first argument in the second argument and if found, returns the position the of the first argument in the second argument. The first character in a string is position 1. Returns 0 if the substring isn’t found in the expression.
LOG	Returns the logarithm of the numeric input expression. If no base is specified, the natural log (ln) will be calculated.
LOG10	Returns the log base 10 of the numeric input expression.
LOWER	Returns the input expression with all the characters converted to lowercase.
LPAD	Left pads a string with spaces or specified characters to reach the number of characters specified as a parameter.
LSHIFT	Shifts the bits of the numeric expression to the left.
LTRIM	Removes leading spaces or characters from a string.
MAP_KEYS	Returns all keys from a map expression.
MAP_VALUES	Returns all values from a map expression.
MASK	Returns a masked version of a string.
MASK_FIRST_N	Returns a masked version of a string with the first num_chars characters masked. By default, if you do not provide a mask value, the first four characters are masked.
MASK_HASH	Returns a consistent hash value based on the input string. This function returns NULL for non-string types.
MASK_LAST_N	Returns a masked version of a string with the last num_chars characters masked. By default, if you do not provide a mask value, the last four characters are masked.
MASK_SHOW_FIRST_N	Returns a masked version of a string with the first num_chars characters unmasked. By default, if you do not provide a value, the first four characters are shown.
MASK_SHOW_LAST_N	Returns a masked version of a string with the last num_chars characters unmasked. By default, if you do not provide a value, the last four characters are shown.
MAX	Returns the maximum value among the non-NULL input expressions.
MD5	Computes the MD5 hash value of a string.
MEDIAN	Computes the median of a dataset.
MIN	Returns the minimum value among the non-NULL input expressions.
MINUTE	Extracts the minute number (from 0 to 59) for a given time or timestamp.
MOD	Returns the remainder of the input expression divided by the second input expression.
MONTH	Extracts the month number (from 1 to 12) for a given date or timestamp.
MONTHS_BETWEEN	Returns the number of months between two date or timestamp values.
NDV	Returns an approximate distinct value number, similar to COUNT(DISTINCT col). NDV can return results faster than using the combination of COUNT and DISTINCT while using a constant amount of memory, resulting in less memory usage for columns with high cardinality.
NEXT_DAY	Returns the date or timestamp of the first specified day of week that occurs after the input date.
NTILE	Equally splits the rows in each partition into ranked parts specified by the integer value and starting from 1. This function requires the ORDER BY clause.
NULLIF	Compares two expressions. If the values in each expression are equal, returns NULL and, if they are not equal, returns the value of the first expression.
OCTET_LENGTH	Returns the length of the string in bytes.
PERCENTILE_CONT	Computes a percentile value based on a continuous distribution of the column input.
PERCENTILE_DISC	Computes a specific percentile for sorted values in a column.
PERCENT_RANK	Returns the relative rank of the current row in the partition based on the ORDER BY clause. The displayed percentage ranges from 0.0 to 1.0.
PI	Returns the value of pi, which is approximately 3.14592654.
POSITION	Returns the position of the first occurrence of a substring within another string.
POWER	Returns the result of raising the input value to the specified power.
QUARTER	Extracts the quarter number (from 1 to 4) for a given date or timestamp.
QUOTE	Returns a result that can be used as a properly escaped data value in a SQL statement.
RADIANS	Convert a value in degrees to radians
RANDOM	Each call returns a random generated number between 0 and 1 for each row.
RANK	Returns the rank of the current row within its partition and placement order. Rows that are equal have the same rank. However, the count of tied rows is added to the next rank, instead of being incremented by one. The rank value starts at 1 and increases sequentially.
REGEXP_EXTRACT	Extracts the first string in expression that matches the REGEXP expression and corresponds to the REGEX group index.
REGEXP_LIKE	Returns true when the specified regular expression matches values in a column. Otherwise, returns false.
REGEXP_MATCHES	Returns true when the specified regular expression matches values in a column. Otherwise, returns false.
REGEXP_REPLACE	Finds strings that match the given regular expression and replaces the strings with the given string.
REGEXP_SPLIT	Splits an input string by using a regular expression according to a keyword and an integer value.
REPEAT	Builds a string by repeating the input for the specified number of times
REPEATSTR	Repeats the given string n times.
REPLACE	Removes all occurrences of a specified substring and replaces them with another string.
REVERSE	Reverses the order of characters in a string.
RIGHT	Returns the right-most substring. The function name must be enclosed in double quotes (“RIGHT”).
ROUND	Returns the rounded value for the inputted value. If no scale is specified, the closest whole number is returned.
ROW_NUMBER	Returns the row number for the current row based on the ORDER BY clause within each partition. Rows containing identical values receive different row numbers.
RPAD	Right pads a string with spaces or specified characters to reach the number of characters specified as a parameter.
RSHIFT	Shifts the bits of the numeric expression to the right.
RTRIM	Removes trailing spaces or characters from a string.
SECOND	Extracts the second number (from 0 to 59) for a given date or timestamp.
SHA	Computes the SHA-1 hash value of a string.
SHA1	Computes the SHA-1 hash value of a string.
SHA256	Computes the 256-bit SHA-2 hash value of a string.
SHA512	Computes the 512-bit SHA-2 hash value of a string.
SIGN	Returns the sign of the input expression.
SIMILAR_TO	Tests whether the entire expression matches a pattern.
SIN	Computes the sine of a value.
SINH	Computes the hyperbolic sine of the input expression.
SIZE	Returns the number of entries in a map expression.
SOUNDEX	Returns a string that contains a phonetic representation of the input string.
SPLIT_PART	Splits a given string at a specified character and returns the requested part.
SQRT	Returns the square root of the non-negative numeric expression.
STARTS_WITH	Returns whether a string starts with another string. The comparison is case-sensitive.
STDDEV	Returns the standard deviation of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STDDEV_POP	Returns the population standard deviation (square root of variance) of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STDDEV_SAMP	Returns the sample standard deviation (square root of sample variance) of non-NULL values in a column with a numeric data type. If all records inside a group are NULL, returns NULL.
STRPOS	Searches for the first occurrence of the substring in the given expression and returns the position of where the substring begins. Searching binary values is also supported.
SUBSTRING	Returns the portion of the string from the specified base expression starting at the specified characters.
SUBSTRING_INDEX	Returns a substring of an expression before the specified number of delimiters occurs.
SUM	Returns the sum of non-NULL input expressions.
TAN	Computes the tangent of a value in radians.
TANH	Computes the hyperbolic tangent of the input expression.
TIMESTAMPADD	Add (or subtract) an interval of time from a date/timestamp value or column.
TIMESTAMPDIFF	Return the amount of time between two date or timestamp values
TOASCII	Converts a string that is encoded in the specified character set to UTF-8.
TO_CHAR	Converts the input expression to a character/string using the specified format.
TO_DATE	Converts the input expressions to the corresponding date.
TO_HEX	Returns a hexadecimal string for the given binary value.
TO_NUMBER	Converts a string into a number (double) in the specified format.
TO_TIME	Converts the input expressions to the corresponding time.
TO_TIMESTAMP	Converts the input expressions to the corresponding timestamp.
TRANSLATE	Translates the base expression from the source characters/expression to the target characters/expression.
TRIM	Removes leading, trailing, or both spaces or characters from a string.
TRUNCATE	Rounds the input expression down the nearest of equal integer depending on the specified number of places before or after the decimal point.
TYPEOF	Reports the type (in string format) of the input expression.
UCASE	Returns the input expression with all the characters converted to uppercase.
UNBASE64	Decodes a Base64-encoded string.
UNHEX	Converts the hexadecimal number into the bytes represented by a number.
UNIX_TIMESTAMP	Returns the Unix epoch time representation of an ISO 8601 timestamp.
UPPER	Returns the input expression with all the characters converted to uppercase.
VAR_POP	Returns the population variance of non-NULL records.
VAR_SAMP	Returns the sample variance of non-NULL records.
WEEK	Extracts the week number (from 0 to 53) for a given date or timestamp.
WEEKOFYEAR	Returns the week of year of the date or timestamp.
XOR	Returns the bitwise XOR of two integers.
YEAR	Extracts the year for a given date or timestamp.