Hive: string functions

 

Below table summarizes different string functions supported by Hive.

 

Function	Description	Example
ascii(string str)	Convert the String first character to numeric ASCII value.	hive> SELECT ascii('ABC'); OK _c0 65 Time taken: 0.039 seconds, Fetched: 1 row(s)
concat(string str1, string str2... )	Concatenate strings	hive> SELECT concat('Hello',' ', 'World'); OK _c0 Hello World Time taken: 0.045 seconds, Fetched: 1 row(s)
concat_ds(string delimiter, string str1, string str2...)	Concatenate strings using given delimiter.	hive> SELECT concat_ws('-','Hello','World','Program'); OK _c0 Hello-World-Program Time taken: 0.041 seconds, Fetched: 1 row(s)
find_in_set(string search_string, string source_string_list)	Search for the occurrence of a string in given string list. This method return 0 if no occurrence found	hive> SELECT find_in_set('World','Hello,World,Program'); OK _c0 2 hive> ; hive> ; hive> SELECT find_in_set('World1','Hello,World,Program'); OK _c0 0 Time taken: 0.044 seconds, Fetched: 1 row(s)
length(string str)	Return the length of a string.	hive> SELECT length('Hello World'); OK _c0 11 Time taken: 0.047 seconds, Fetched: 1 row(s)
lcase(string str)	Return the string in lowercase.	hive> SELECT lcase('HEllo'); OK _c0 hello Time taken: 0.043 seconds, Fetched: 1 row(s)
lower(string str)	Return the string in lowercase.	hive> SELECT lower('HEllo'); OK _c0 hello Time taken: 0.052 seconds, Fetched: 1 row(s)
lpad(string str, int len, string pad )	Pad the characters to the string at left side until the string reaches to given length	hive> SELECT lpad('He', 5, 'X'); OK _c0 XXXHe Time taken: 0.043 seconds, Fetched: 1 row(s)
ltrim(string str)	Remove all the left side trailing spaces in the string.	hive> SELECT concat(ltrim('  Hello    '), 'world'); OK _c0 Hello    world Time taken: 0.039 seconds, Fetched: 1 row(s)
repeat(string str, int n)	Repeat a string n times.	hive> SELECT repeat('Hi', 5); OK _c0 HiHiHiHiHi Time taken: 0.049 seconds, Fetched: 1 row(s)
reverse(string str)	Reverse the string	hive> SELECT reverse('Hello'); OK _c0 olleH Time taken: 0.038 seconds, Fetched: 1 row(s)
rpad(string str, int len, string pad)	Pad the characters to the string at right side until the string reaches to given length	hive> SELECT rpad('He', 5, 'X'); OK _c0 HeXXX Time taken: 0.04 seconds, Fetched: 1 row(s)
rtrim(string str)	Remove all the right side trailing spaces in the string.	hive> SELECT concat(rtrim('  Hello    '), 'world'); OK _c0   Helloworld Time taken: 0.049 seconds, Fetched: 1 row(s)
space(int n)	Return ‘n’ number of spaces.	hive> SELECT concat('Hello', space(10), 'world'); OK _c0 Hello          world Time taken: 0.039 seconds, Fetched: 1 row(s)
split(string str, string separator)	Split the string by a separator and return array of strings as output.	hive> SELECT split('ram,23,india', ','); OK _c0 ["ram","23","india"] Time taken: 0.039 seconds, Fetched: 1 row(s)
substr (string sourceStr, int startPosition [,int length])	Return substring from start position to length number of characters. If the length is not specified, then it considers size of string as length.	hive> SELECT substr('Hello World', 4, 5); OK _c0 lo Wo Time taken: 0.051 seconds, Fetched: 1 row(s) hive> ; hive> ; hive> SELECT substr('Hello World', 4); OK _c0 lo World Time taken: 0.046 seconds, Fetched: 1 row(s)
substring (string sourceStr, int startPosition [,int length])	Same as substr	hive> SELECT substring('Hello World', 4, 5); OK _c0 lo Wo Time taken: 0.039 seconds, Fetched: 1 row(s) hive> ; hive> SELECT substring('Hello World', 4); OK _c0 lo World Time taken: 0.038 seconds, Fetched: 1 row(s)
trim(string str)	Trim all trailing and leading spaces	hive> SELECT concat(trim('    Hello    '), 'World'); OK _c0 HelloWorld
ucase(string str)	Return the string in upper case.	hive> SELECT ucase('Hello'); OK _c0 HELLO Time taken: 0.04 seconds, Fetched: 1 row(s)
upper(string str)	Same as ucase.	hive> SELECT upper('Hello'); OK _c0 HELLO Time taken: 0.048 seconds, Fetched: 1 row(s)

 

 

 

 

Previous                                                    Next                                                    Home