How To Convert Raw Data In Hive To String

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

String office in Hive will help you in computations and will aid to fetch outcome faster. You can try these String function in Hive examples on your own and comment on whatsoever issues.

String Functions In Hive Allow'due south start and meet some of the important String function in Hive with syntax and examples.

Contents

1 Top String function in Hive
- i.1 ASCII
- ane.two CONCAT
- 1.3 CONCAT_WS
- 1.4 LENGTH
- 1.5 FIND_IN_SET
- 1.6 LOWER/LCASE
- ane.vii LPAD
- ane.8 RPAD
- one.ix TRIM
- ane.10 REPEAT
- 1.11 REVERSE
- i.12 Infinite
- 1.13 Carve up
- 1.14 SUBSTR /SUBSTRING
- one.fifteen FORMAT_NUMBER

Top String function in Hive

Hither are the important cord functions in Hive with examples. At the end of all these Hive string functions, we accept shared some examples which you lot tin can endeavour. These are straight forward and you lot should not face up 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 part concatenates all the strings/cavalcade and results in the concatenated cord equally a outcome.

Example: hive> select CONCAT(name,'+',location) from HiveTbl1; John + Illions hive> select CONCAT(proper noun, '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 role. The difference is, you can also provide the string delimiter which will be applied in betwixt the string.

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

LENGTH

Syntax: LENGTH (cord str)

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

Example:
hive> select LENGTH(name) from HiveTbl1; 4 5 half dozen 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 outset occurrence of search_string in source_string_list. FIND_IN_SET will return the position of the beginning occurrence. If not found, it will return 0. Here source_string_list should be comma delimited.

Example:
hive> select FIND_IN_SET('hi','ho,hullo,ha,bye'); 2 hive> select FIND_IN_SET(John,proper name) from HiveTbl1; ane 0 0

LOWER/LCASE

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

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

Case:
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.

Instance:
hive> select LPAD('hdfs',six,'Z') ZZhdfs

RPAD

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

Details: RAPD function returns the string with the length as "len" after correct padding it with string pad.

Case:
hive> select LPAD('hdfs',6,'Z') hdfsZZ

TRIM

Syntax:TRIM/ LTRIM/RTRIM (string str)

Details: TRIM Hive function removes the spaces from the cord. If you lot are just putting TRIM so it volition remove the spaces from the cord. Over again information technology has two parts- LTRIM and RTRIM. LTRIM removes the spaces from the left of the string while RTRIM removes from the correct 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 (cord str, int n)

Details: Repeat role repeats the string "str" to "n" number of times.

Example:
hive> select Repeat(hdfs,2); hdfshdfs

Reverse

Syntax: Contrary (string str)

Details: Contrary Hive function opposite the cord "str"

Example:
hive> select Reverse('hdfs'); sfdh

Space

Syntax: Space (int number_of_spaces)

Details: SPACE part returns the specified number of spaces.

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

Divide

Syntax: Dissever ('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( cord 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 offset position to the stop of the string.

Example:
hive> select SUBSTR(name,four) from HiveTbl1; oh ui hive> select SUBSTR(HDFSTUTORIAL,2,iii); FSTUTOR

FORMAT_NUMBER

Syntax: FORMAT_NUMBER(number 10, int D)

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

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

These were few top string function in Hive which you tin can apply in your projects. Try these Hive cord functions and allow us know if yous will face any issue.
Also, if you lot have used any other Hive string role, comment here and we volition include information technology here.

Ad:

Get an instant remote access to your favorite programming tools such every bit IDE, emulators etc. by loading them onto cloud hosted citrix vdi from CloudDesktopOnline at an inexpensive citrix xendesktop pricing with 24*7*365 days meridian-notch support from https://www.apps4rent.com.