Skip to main content
Skip table of contents

Built-in SQL Functions

 

NameReturn typeDescriptioncomments
ABS(DOUBLE a)DOUBLEReturns 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 CBOOLNULL 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)INTReturns the number of UTF-8 characters contained in str
COALESCE(BOOL v1, BOOL v2, ...)BOOLReturns the first v that is not NULL, or NULL if all v's are NULL.
CONCAT(string|binary A, string|binary B...)stringReturns the string resulting from concatenating the strings passed in as parameters in order.
CURRENT_USER()stringReturns current user name from the configured authenticator manager
DATE_ADD(date/timestamp/string startdate, INT days)stringAdds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'.
DATEDIFF(string enddate, string startdate)INTReturns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2.
DAY(string date) DAYOFMONTH(date)INTReturns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1
DATE_FORMAT( DATE date, STRING format)stringReturns formatted elements of the date. (See additional note)
FLOOR(DOUBLE a)BIGINTReturns the maximum BIGINT value that is equal to a
FROM_UNIXTIME(BIGINT unixtime[, string format])stringConverts 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)INTReturns 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)BOOLReturns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.
INSTR(string str, string substr)INTReturns 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)BOOLReturns True if value is NULL
ISNOTNULL(value)BOOLReturns True if value is not NULL
LAST_DAY(string date)stringReturns 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 BBOOLNULL 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)stringReturns 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])INTReturns the position of the first occurrence of substr in str after position pos.
LOWER(string A) LCASE(string A)stringReturns the string resulting from converting all characters of B to lower case.
LPAD(string str, INT len, string pad)stringReturns 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)INTReturns the minute of the timestamp.
MONTH(string date)INTReturns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11
MONTHS_BETWEEN(date1, date2)DOUBLEReturns 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)stringReturns the first date which is later than start_date and named as day_of_week
NOT ABOOLTRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.
NVL(BOOL value, BOOL default_value)BOOLReturns default value if value is NULL else returns value
PMOD(INT a, INT b), PMOD(DOUBLE a, DOUBLE b)INT or DOUBLEReturns the positive value of a mod b.
POW(DOUBLE a, DOUBLE p), POWER(DOUBLE a, DOUBLE p)DOUBLEReturns a power p
QUARTER(date/timestamp/string)INTReturns 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)stringReturns 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)stringReturns 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)DOUBLEReturns the rounded BIGINT value of a.
ROUND(DOUBLE a, INT d)DOUBLEReturns a rounded to d decimal places.
RPAD(string str, INT len, string pad)stringReturns 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)INTReturns the second of the timestamp.
SIGN(DOUBLE a), SIGN(DECIMAL a)DOUBLE or INTReturns 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)arraySplits str around pat (pat is a regular expression).
SQRT(DOUBLE a), SQRT(DECIMAL a)DOUBLEReturns the square root of a
SUBSTR(string|binary A, INT start), SUBSTRING(string|binary A, INT start)stringReturns 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)stringReturns 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)stringReturns date truncated to the unit specified by the format. Supported formats: MONTH/MON/MM, YEAR/YYYY/YY
UNHEX(string A)BINARYInverse 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()BIGINTGets current Unix timestamp in seconds.
UPPER(string A) UCASE(string A)stringReturns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'.
WEEKOFYEAR(string date)INTReturns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44
YEAR(string date)INTReturns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.