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 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:
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 MD5 (string | String |
Example: Example
SQL
Syntax
SQL
By modifying a character, this print changes radically : Syntax
SQL
| |
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 |