Hive String Functions

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 –

1. PostgreSQL String Functions
2. Hive Interview Questions
3. JavaScript String Functions
4. Examples Of VB.Net String Functions
5. How To Use VBScript String Functions
6. Components of Hive Architecture
7. Hive Order By
8. Hive Installation