All Functions

Function Name Description
ABS Computes the absolute value of a numeric expression.
ACOS Computes the arcccosine (inverse cosine) of a value in radians
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.
BINARY_STRING Converts the input expression to a binary value.
BIT_LENGTH Gets length of bits of the input expression
BOOL_AND Computes the boolean AND of two boolean expressions. Returns TRUE if both epxressions 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 epxression(s) evaluate(s) to TRUE. Returns FALSE if both expressions evaluate to FALSE.
BTRIM Trims leading and trailing characters from a string.
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.
CONCAT Concatenates two or more strings
CONTAINS Returns TRUE if the first expression contains the second expression.
CONVERT_TIMEZONE Convert timestamp to the specified timezone
CORR Calculates the covariance 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.
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_SCHEMA Returns the path/schema in use by the current session.
CURRENT_TIME Returns the current time for the system.
CURRENT_TIMESTAMP Returns the current timestamp for the system.
DATE_ADD Add (or subract) days from a date/timestamp value or column
DATE_PART Return subfields such as year or hour from date or timestamp values
DATE_SUB Subtracts the number of days from the specified date or timestamp.
DATE_TRUNC Truncates the date or timestamp to the indicated precision.
DEGREES Converts radians to degrees.
E Returns Euler’s number, a constant approximately equal to 2.718281828459045.
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.
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
HLL Uses HyperLogLog to return an approximation of the distint cardinality of the input.
ILIKE Compares two strings and returns true if they match.
INITCAP Returns the input string with the first letter of each word in uppercase and the subsequent letters in the word are in lowercase).
ISFALSE Returns TRUE if the input expression is FALSE.
ISNULL Determines if an expression is NULL. Returns true if is NULL, and false otherwise. Alias for the operator IS NULL.
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_UTF8 Returns whether an expression is valid UTF-8
IS_VARCHAR Returns TRUE if the input expression is a varchar value.
LAST_QUERY_ID Returns the ID for the most recently executed query in the current session.
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.
LOCALTIME Returns the current time for the system.
LOCALTIMESTAMP Returns the current timestamp for the system.
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 chracters specified as a parameter.
LSHIFT Shifts the bits of the numeric expression to the left.
LTRIM Removes leading spaces or characters from a string.
MAX Returns the maximum value for the non-NULL input expressions.
MEDIAN Computes a percentile based on a continuous distribution of the column value
MIN Returns the minimum value for the non-NULL input expressions.
MOD Returns the remainder of the input expression divided by the second input expression.
NOW Returns the current timestamp (date and time) in UTC timezone.
OCTET_LENGTH Returns the length of the string in bytes.
PERCENTILE_CONT Computes a percentile based on a continuous distribution of the column value
PERCENTILE_DISC Computes a specific percentile for sorted values in a column
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.
QUERY_USER Returns the username of the user that is currently logged in to the system.
RADIANS Convert a value in degrees to radians
RANDOM Each call returns a random generated number between 0 and 1 for each row.
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.
RPAD Right pads a string with spaces or specified characters to reach the number of chracters specified as a parameter.
RSHIFT Shifts the bits of the numeric expression to he right.
RTRIM Removes trailing spaces or characters from a string.
SESSION_USER Returns the username of the user that created the current session.
SIGN Returns the sign of the input expression.
SIN Computes the sine of a value.
SINH Computes the hyperbolic sine of the input expression.
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.
STRPOS Searches for the first occurence 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 chracters.
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
TO_CHAR Converts the input expression to a character/string using the specified format.
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_TIMESTAMP Converts the input expressions to the corresponding timestamp.
TRANSACTION_TIMESTAMP Returns the timestamp in UTC of the current transaction.
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.
UNIX_TIMESTAMP Returns the Unix timestamp for the timestamp parameter.
UPPER Returns the input expression with all the characters converted to uppercase.
USER Returns the user that is currently logged into the system.
VAR_POP Returns the population variance of non-NULL records.
VAR_SAMP Returns the sample variance of non-NULL records.
XOR Returns the bitwise XOR of two integers.