Top String Function in Hive To Use While Data Analysis

In this post, we will be discussing some of the important string function in the hive with examples and syntax.

String function in Hive will help you in computations and will help to fetch result faster. You can try these String function in Hive examples on your own and comment on any issues.

Let’s start and see some of the important String function in Hive with syntax and examples.

Top String function in Hive

Here are the important string functions in Hive with examples. At the end of all these Hive string functions, we have shared some examples which you can try. These are straight forward and you should not face any issue. But if you do, share the error in the comment section.

ASCII

Syntax: ASCII (string str)

Details: ASCII returns the ASCII value of the first character of the string.

Example:

hive> select ASCII(‘hadoop’) from HiveTbl1
104
hive> select ASCII(‘A’) from HiveTbl2
65


CONCAT

Syntax: CONCAT (string str1, string str2, string str3…)

Details: CONCAT function concatenates all the strings/column and results in the concatenated string as a result.

Example:
hive> select CONCAT(name,'+',location) from HiveTbl1;
John + Illions
hive> select CONCAT(name, ‘is from’, ‘’, location) from HiveTbl1;
John is from Illions


CONCAT_WS

Syntax: CONCAT_WS (string delimiter, string str1, string str2…)

Details: CONCAT_WS is similar to the CONCAT function. The difference is, you can also provide the string delimiter which will be applied in between the string.

Example:

hive> select CONCAT_WS('-',name,location) from HiveTbl1;
John-Illions


LENGTH

Syntax: LENGTH (string str)

Details: LENGTH function in Hive returns the number of characters in the string

Example:

hive> select LENGTH(name) from HiveTbl1;
4
5
6
hive> select LENGTH(hdfs);
4


FIND_IN_SET

Syntax: FIND_IN_SET (string search_string, string source_string_list)

Details: This is used to find the first occurrence of search_string in source_string_list. FIND_IN_SET will return the position of the first occurrence. If not found, it will return 0. Here source_string_list should be comma delimited.

Example:

hive> select FIND_IN_SET('hi','ho,hi,ha,bye');
2
hive> select FIND_IN_SET(John,name) from HiveTbl1;
1
0
0


LOWER/LCASE

Syntax: LOWER( string str ), LCASE( string str )

Details: LOWER or LCASE do the same work. This converts the string into lower case letters.

Example:

hive> select LOWER(HDFS);
hdfs


LPAD

Syntax: LPAD (string str, int len, string pad)

Details: LAPD function returns the string with the length as “len” after left padding it with string pad.

Example:

hive> select LPAD(‘hdfs’,6,'Z')
ZZhdfs

 

RPAD

Syntax: RPAD (string str, int len, string pad)

Details: RAPD function returns the string with the length as “len” after right padding it with string pad.

Example:

hive> select LPAD(‘hdfs’,6,'Z')
hdfsZZ

 

TRIM

Syntax:TRIM/ LTRIM/RTRIM (string str)

Details: TRIM Hive function removes the spaces from the string. If you are just putting TRIM then it will remove the spaces from the string. Again it has two parts- LTRIM and RTRIM. LTRIM removes the spaces from the left of the string while RTRIM removes from the right of the string.

Example:

hive> select TRIM(' hive ') from HiveTbl1;
hive
hive> select LTRIM(' hive') from HiveTbl1;
hive
hive> select RTRIM('hive ') from HiveTbl1;
hive

 

REPEAT

Syntax: REPEAT (string str, int n)

Details: REPEAT function repeats the string “str” to “n” number of times.

Example:

hive> select REPEAT(hdfs,2);
hdfshdfs

 

REVERSE

Syntax: REVERSE (string str)

Details: REVERSE Hive function reverse the string “str”

Example:

hive> select REVERSE(‘hdfs’);
sfdh

 

SPACE

Syntax: SPACE (int number_of_spaces)

Details: SPACE function returns the specified number of spaces.

Example:

hive> select space(10),name from HiveTbl1;
john (after john, 10 space character are there)

 

SPLIT

Syntax: SPLIT (‘string1:string2’, pat)

Details: SPLIT function split the string depending on the pat shared.

Example:

hive> select split('hadoop:hdfs,',') from HiveTbl1;
Hadoop,hdfs

 

SUBSTR /SUBSTRING

Syntax:

SUBSTR (string source_str, int start_position [,int length])
SUBSTRING( string source_str, int start_position [,int length])

Details: The SUBSTR or SUBSTRING function returns a part of the source string from the start position with the specified length of characters. If the length is not given, then it returns from the start position to the end of the string.

Example:

hive> select SUBSTR(name,4) from HiveTbl1;
oh
ui
hive> select SUBSTR(HDFSTUTORIAL,2,3);
FSTUTOR

 

FORMAT_NUMBER

Syntax: FORMAT_NUMBER(number X, int D)

Details: FORMAT function format the number X to a format like #,###,###.##, rounded to D decimal places and returns result as a string.
If D=0 then the value will only have fraction part there will not be any decimal part.

Example:

hive> select name, Format_number(Hike,0) from HiveTbl1;
John 40,000

These were few top string function in Hive which you can use in your projects. Try these Hive string functions and let us know if you will face any issue.
Also, if you have used any other Hive string function, comment here and we will include it here.