Introduction to Hive String Function
The string is a sequence of characters. In hive, string functions are used to 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 given string with N number of time.
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 is used to remove all the spaces from the string and gives new string with no spaces.
Syntax:
Trim(string str);
E.g.:
Trim('Apple');
Output:
Apple
4. Rtrim
This Rtrim function is used to remove all the spaces from the right-hand side of the string and gives new string.
Syntax:
RTRIM(string str);
E.g.:
RTRIM('Apple');
Output:
Apple
5. LTRIM
This LTRIM function is used to remove all the spaces from the left-hand side of the string and gives new string.
Syntax:
LTRIM(stringstr);
E.g.:
LTRIM('Apple');
Output:
Apple
6. LOWER or LCASE
This string function is used to 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 is used to convert 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 delimiter which 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 that are present 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 Space in 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 is used to give the string with a length of line characters left-padded with pad character.
Syntax:
LPAD(string str,int len,string pad)
E.g:
LPAD(‘Hadoop’,8,’H’);
Output:
hhhadoop
14. RPAD
The RPAD function is used to give the string with a length of len characters right-padded with pad character.
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 arguments of the string is null then it gives NULL as an output. If sub str is missing or cannot be found in str then 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 is used to give 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 is used to translate the characters of a given string with corresponding characters mentioned in the to string. If any argument is NULL in a given string, then it will give 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, a timestamp is in the format as follows (2019-01-01 00:00:00 UTC) then 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 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 example and output. You can also go through our other Suggested Articles to learn more –
