Updated May 26, 2023
Introduction to Hive String Function
The string is a sequence of characters. In the Hive, string functions perform different operations like reversing sting, converting into upper and lower case, removing spaces, etc. Hive string functions look like SQL string functions. Let us look at those string functions in detail to understand the concept easily.
Hive String Functions
Below are some hive string functions with some examples.
1. Repeat
This hive String function is used to repeat a given string with N number of times.
Syntax:
Repeat(string str, n);e.g.:
Repeat('Apple',3);Output: Apple Apple Apple
2. Reverse
This Reverse function will reverse the given string input and gives the result, as shown below.
Syntax:
Reverse(string str);E.g.:
ReverseE('Apple');Output: elppA
3. Trim
This Trim function removes all the spaces from the string and gives a new string with no spaces.
Syntax:
Trim(string str);E.g.:
Trim('Apple');Output:
Apple
4. Rtrim
This Rtrim function removes all the spaces from the right-hand side of the string and gives a new string.
Syntax:
RTRIM(string str);E.g.:
RTRIM('Apple');Output:
Apple
5. LTRIM
This LTRIM function removes all the spaces from the left-hand side of the string and gives a new string.
Syntax:
LTRIM(stringstr);E.g.:
LTRIM('Apple');Output:
Apple
6. LOWER or LCASE
This string function converts all the characters of the given string into the lower letter case.
Syntax:
LOWER(string str);or LCASE(string str);E.g.:
LOWER('ApPle'); or LCASE('ApPle');Output:
apple
7. UPPER or UCASE
This UPPER function converts all the characters of the given string into the Upper Letter Case.
Syntax:
UPPER(string str);or UCASE(string str);E.g.:
UPPER('apple'); or UCASE('apple');Output:
APPLE
8. CONCAT
All the strings are integrated with the CONCAT Function.
Syntax:
CONCAT(stringstr1,stringstr2,stringstr3….)E.g.:
CONCAT( 'Apple' , '-' , 'eat' );Output:
‘Apple-eat’
9. CONCAT_WS
CONCAT function and CONCAT_WS function both have the same properties. In this CONCAT_WS function, you can provide a delimiter that can be used in between the strings to CONCAT
E.g.:
CONCAT_WS( '-' , 'apple' , 'eat' );Output:
‘apple-eat’
10. LENGTH
This LENGTH function is used to get the number of characters in the given string.
Syntax:
LENGTH(string str);E.g.:
LENGTH( 'Apple' );Output:
5
11. SPACE
This SPACE function is used to give the specified number of Spaces in the string
Syntax:
SPACE(intnum_of_spaces);E.g.:
SPACE(5);Output:
‘ ‘
12. SPLIT
The SPLIT function splits the string around the pattern pat and returns an array of strings. You can specify regular expressions as patterns.
Syntax:
SPLIT(string str, string pat)Example:
SPLIT('hive:Hadoop',':'Output:
[“hive,” “Hadoop”]13. LPAD
The LPAD function gives the string a length of line characters left-padded with pad characters.
Syntax:
LPAD(string str,int len,string pad)E.g.:
LPAD(‘Hadoop,’ 8,’H’);
Output:
hhhadoop
14. RPAD
The RPAD function gives the string a length of len characters right-padded with pad characters.
Syntax:
RPAD(string str, int len, string pad)Output:
hadooppp
15. INSTRING
This function is used to give the position of the first occurrence of a substring in str. If one of the string’s arguments is null, it gives NULL as an output. If sub-str is missing or cannot be found in str, it will return 0. The index value of the first character in str is 1.
Syntax:
INSTR(string str, string substring);E.g.:
INSTR('Rachel','el');Output:
4
16. Locate
This function gives the position of the first occurrence of a substring in the string after position pos.
Syntax:
LOCATE(string substring, string str[,int pos]);E.g.:
LOCATE('el','Rachel',2);Output:
4
17. Translate
This function translates the characters of a given string with corresponding characters mentioned in the string. If any argument is NULL in a given string, it will provide NULL as an output.
Syntax:
TRANSLATE(string|char|varchar input, string|char|varchar from, string|char|varchar to);E.g.:
TRANSLATE('Make sure u knew that code','e','o');Output:
Mako, suro u know that codo
18. ASCII(string str);
The ASCII function is used to give the ASCII value of the first character of a given string.
Example1:
ASCII('hadoop') ;Output:
104
Example2:
ASCII('A') ;Output:
65
19. PRINTF
Returns the input format according to PRINTF style format strings.
Syntax:
PRINTF(Obj...args);20. regxp_replace
Syntax:
regxp_replace(string A, string B, String C)This function returns the string, which is the output of replacing all substrings in B, which matches the Java regular syntax with C.
21. From_unix time
Syntax:
from_unixtime(int Unix time)This function converts the number of seconds from the Unix epoch to a string representing the timestamp of the current system time zone. For example, if a timestamp is in the format as follows (2019-01-01 00:00:00 UTC), this function will convert it to the format of “2019-01-01 00:00:00.”
E.g.:
from_unixtime(int 2011-01-06 12:00:00 UTC)Output:
2011-01-06 12:00:00
22. get_json_object
Syntax:
get_json_object(string json_string, string path)Based on the specified JSON path, this function extracts the JSON object from a JSON string and returns a JSON string of the extracted JSON object. If JSON strings are invalid, then it will return NULL.
23. To_Date
Syntax:
to_date(string timestamp)This function will return the date part of a timestamp string.
E.g.:
to_date("2019-03-04 00:04:12)Output:
2019-03-04
Recommended Articles
This has been a guide to Hive String Functions. Here we discussed the basic concept and various string functions used in Hive with examples and output. You can also go through our other Suggested Articles to learn more –
