Hive String Functions and Examples

  • Post author:
  • Post last modified:October 28, 2019
  • Post category:BigData
  • Reading time:4 mins read

In this article, we will discuss on the various Hive string functions and usage. The HQL string functions are similar to the SQL string functions.

Hive String Functions

The string functions in Hive are listed below:

Read:

Hive concat (string A, string B,…) Function

This Hive built-in strig function cocatenates all the given strings:

hive> select CONCAT('concat','->','demo');
OK
concat->demo

Hive substr(string, int start, int end) Function

This function returns the substring of A starting from start position with the given length i.e. end postion. Start and end postion are integer values.

Related reading:

hive> select substr('This is hive demo',9,4);
OK
hive

Hive length(string A ) Function

The length function returns the number of characters in a string.

hive> select length('hadoop');
OK
6

Hive upper(string A) and ucase(string A) Functions

These functions returns the string resulting from converting all characters of A to upper case string.

hive> select upper('hadoop'), ucase('hadoop');
OK
HADOOP HADOOP

Hive lower(string A) and lcase(string A) Functions

These functions returns the string resulting from converting all characters of A to lower case string.

hive> select lower('HADOOP'), lcase('HADOOP');
OK
hadoop hadoop

Hive lpad(string A, int len, string pad ) Function

The lpad function returns the string with a length of len characters left-padded with pad character.

hive> select lpad('hadoop',8,'H');
OK
Hhhadoop

Hive rpad(string A, int len, string pad ) Function

The rpad function returns the string with a length of len characters right-padded with pad character.

hive> select rpad(‘hadoop’,8,’p’);
OK
hadooppp

Hive trim(string A) Function

This function returns the string resulting from removing spaces from both ends of string A.

hive> select trim(' Hadoop ');
OK
'Hadoop'

Hive ltrim(string A) Function

This function returns the string resulting from removing spaces from left ends of string A.

hive> select ltrim(' Hadoop ');
OK
'Hadoop '

Hive rtrim(string A) Function

This function returns the string resulting from removing spaces from right ends of string A.

hive> select rtrim(' Hadoop ');
OK
' Hadoop'

Hive repeat( string A, int n ) Function

The repeat function repeats the specified string n times.

hive> select repeat('Hadoop',2);
OK
HadoopHadoop

Hive reverse( string A) Function

The reverse function gives the reversed string.

hive> select reverse('Hadoop');
OK
poodaH

Hive split(string A, string pattern) Function

The split function splits the string around the pattern pat and returns an array of strings. You can also specify regular expressions as patterns.

hive> select split('hadoop~supports~split~function','~');
OK
["hadoop","supports","split","function"]

Also Read:

Tags: ,