Built-in SQL Functions

Name	Return type	Description	comments
ABS(DOUBLE a)	DOUBLE	Returns the absolute value.
Ascii(string str)	INT	Returns the numeric value that is the representation of the ASCII characterof the first character of `<str>` For example, if the input is the letter ‘a’, then the return value is 97.	The value 0 is returned for either of the following cases: The first character of the string contains the ASCII character corresponding to 0. The string is empty. To distinguish between these two cases, use the LENGTH function to determine whether the string is empty.
A [NOT] BETWEEN B AND C	BOOL	NULL if A, B or C is NULL, TRUE if A is greater than or equal to B AND A less than or equal to C, otherwise FALSE. This can be inverted by using the NOT keyword
CHARACTER_LENGTH(string str)	INT	Returns the number of UTF-8 characters contained in str
COALESCE(BOOL v1, BOOL v2, ...)	BOOL	Returns the first v that is not NULL, or NULL if all v's are NULL.
CONCAT(string\|binary A, string\|binary B...)	string	Returns the string resulting from concatenating the strings passed in as parameters in order.
CURRENT_USER()	string	Returns current user name from the configured authenticator manager
DATE_ADD(date/timestamp/string startdate, INT days)	string	Adds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'.
DATEDIFF(string enddate, string startdate)	INT	Returns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2.
DAY(string date) DAYOFMONTH(date)	INT	Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1
DATE_FORMAT( DATE date, STRING format)	string	Returns formatted elements of the date. (See additional note)
FLOOR(DOUBLE a)	BIGINT	Returns the maximum BIGINT value that is equal to a
FROM_UNIXTIME(BIGINT unixtime[, string format])	string	Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".
HOUR(string date)	INT	Returns the hour of the timestamp: hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12.
HASH (string `value\| int value`) MD5 (string `value \| int value`)	String	Hash function is a function that can be used to map data of arbitrary size to fixed-size values. The values are usually used to index a fixed-size table called a hash table. MD5 (Message Digest 5) is a cryptographic hash function that calculates, from a digital file, its digital fingerprint (in this case a sequence of 128 bits or 32 characters in hexadecimal notation) with a very high probability that two different files give two different fingerprints. Example: Example SQL `SELECT MD5(12345); >827ccb0eea8a706c4c34a16891f84e7b` Syntax SQL `select MD5("Et l’unique cordeau des trompettes marines") ; >8747e564eb53cb2f1dcb9aae0779c2aa` By modifying a character, this print changes radically : Syntax SQL `select MD5("Et l’unique cordeau des trompettes marinEs"); >c802e1bd9b5f2b0d244bbc982f5082b3` Returns a hash value of the arguments. (As of Hive 0.4.)
IF(boolean testCondition, BOOL valueTrue, BOOL valueFalseOrNull)	BOOL	Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.
INSTR(string str, string substr)	INT	Returns the position of the first occurrence of substr in str. Returns null if either of the arguments are null and returns 0 if substr could not be found in str.
ISNULL(value)	BOOL	Returns True if value is NULL
ISNOTNULL(value)	BOOL	Returns True if value is not NULL
LAST_DAY(string date)	string	Returns the last day of the month which the date belongs to. date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time part of date is ignored.
A [NOT] LIKE B	BOOL	NULL if A or B is NULL, TRUE if string A matches the SQL simple regular expression B, otherwise FALSE. The comparison is done character by character.
LTRIM(string A)	string	Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(' foobar ') results in 'foobar '.
LOCATE(string substr, string str[, INT pos])	INT	Returns the position of the first occurrence of substr in str after position pos.
LOWER(string A) LCASE(string A)	string	Returns the string resulting from converting all characters of B to lower case.
LPAD(string str, INT len, string pad)	string	Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters. In case of empty pad string, the return value is null.
MINUTE(string date)	INT	Returns the minute of the timestamp.
MONTH(string date)	INT	Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11
MONTHS_BETWEEN(date1, date2)	DOUBLE	Returns number of months between date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the UDF calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2. date1 and date2 type can be date, timestamp or string in the format 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'. The result is rounded to 8 decimal places. Example: months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677
NEXT_DAY(string start_date, string day_of_week)	string	Returns the first date which is later than start_date and named as day_of_week
NOT A	BOOL	TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.
NVL(BOOL value, BOOL default_value)	BOOL	Returns default value if value is NULL else returns value
PMOD(INT a, INT b), PMOD(DOUBLE a, DOUBLE b)	INT or DOUBLE	Returns the positive value of a mod b.
POW(DOUBLE a, DOUBLE p), POWER(DOUBLE a, DOUBLE p)	DOUBLE	Returns a power p
QUARTER(date/timestamp/string)	INT	Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4. Example: quarter('2015-04-08') = 2.
REGEXP_EXTRACT(string subject, string pattern, INT index)	string	Returns the string extracted using the pattern. For example, regexp_extract('foothebar', 'foo(.*?)(bar)', 2) returns 'bar.'
REGEXP_REPLACE(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	string	Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace("foobar", "oo\|ar", "") returns 'fb.'
ROUND(DOUBLE a)	DOUBLE	Returns the rounded BIGINT value of a.
ROUND(DOUBLE a, INT d)	DOUBLE	Returns a rounded to d decimal places.
RPAD(string str, INT len, string pad)	string	Returns str, right-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters.
SECOND(string date)	INT	Returns the second of the timestamp.
SIGN(DOUBLE a), SIGN(DECIMAL a)	DOUBLE or INT	Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE.
SPLIT(string str, string pat)	array	Splits str around pat (pat is a regular expression).
SQRT(DOUBLE a), SQRT(DECIMAL a)	DOUBLE	Returns the square root of a
SUBSTR(string\|binary A, INT start), SUBSTRING(string\|binary A, INT start)	string	Returns the substring or slice of the byte array of A starting from start position till the end of string A. For example, substr('foobar', 4) results in 'bar'
TO_DATE(string timestamp)	string	Returns the date part of a timestamp string (pre-Hive 2.1.0): to_date("1970-01-01 00:00:00") = "1970-01-01".
TRUNC(string date, string format)	string	Returns date truncated to the unit specified by the format. Supported formats: MONTH/MON/MM, YEAR/YYYY/YY
UNHEX(string A)	BINARY	Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number.
UNION [ALL]	--	Combine the result from multiple SELECT statements into a single result set.
UNIX_TIMESTAMP()	BIGINT	Gets current Unix timestamp in seconds.
UPPER(string A) UCASE(string A)	string	Returns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'.
WEEKOFYEAR(string date)	INT	Returns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44
YEAR(string date)	INT	Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970