If you have been working on other RDBMS like Oracle, Redshift etc then you will be surprised to know Hive does not support LEFT-RIGHT functions. You will either should write your own UDF’s using Java or find out any other alternatives. In this article, we will check Apache Hive LEFT-RIGHT functions alternative with some examples.
Hive LEFT-RIGHT Functions Alternatives
Since Hive does not support LEFT-RIGHT function, you could use Hive SUBSTR string function or regexp_extract regular expression function to select leftmost or rightmost characters from the string values. Other possible way is to write your own Java UDF for LEFT-RIGHT functionality.
Related Reading:
Hive LEFT-RIGHT Functions Alternative – SUBSTR String Function
Below is the Hive SUBSTR string function syntax that we are going to use as a Hive LEFT-RIGHT functions alternative:
substr(string|binary A, int start, int len);
Hive LEFT-RIGHT Functions Alternative Syntax
LEFT(col,int len) --> substr(col, 0, int len); RIGHT(col, int len) --> substr(col, (length(colu)-int len+1), (length(col) -1 ));
Hive LEFT-RIGHT Functions Alternative using SUBSTR Examples
Below are the examples that allows you to implement LEFT-RIGHT using Hive SUBSTR function. An example shows how to select leftmost 5 digits using hive string functions:
Hive LEFT Function Examples
Below is the Hive LEFT function alternative example using substr function:
hive> Select substr('1234567890',0,5);
OK
12345
Time taken: 1.559 seconds, Fetched: 1 row(s)
Hive RIGHT Function Examples
Below is the Hive RIGHT function alternative example using substr function:
hive> select substr(string1,(length(string1)-5+1),length(string1)-1) from (select '1234567890' as string1) ab; OK 67890 Time taken: 0.152 seconds, Fetched: 1 row(s)
Hive LEFT-RIGHT Functions Alternative – regexp_extract Regular Expression Function
The other possible way is to use substring function. Below is the Syntax for regexp_extract function:
regexp_extract(string subject, string pattern, int index);
Related reading:
Hive LEFT-RIGHT Functions Alternative using regexp_extract Examples
Below are the examples that allows you to implement LEFT-RIGHT using Hive regexp_extract Regular Expression Function.
Apache Hive LEFT Function Examples
Below is the example to select leftmost 5 digits using regexp_extract function:
hive> select regexp_extract(string1,'[0-9]{5}',0) from (select '1234567890' as string1) ab;
OK
12345
Time taken: 0.239 seconds, Fetched: 1 row(s)
Apache Hive RIGHT Function Examples
Below is the example to select rightmost 5 digits using regexp_extract function:
hive> select regexp_extract(string1,'[0-9]{5}[ ]*$',0) from (select '1234567890' as string1) ab;
OK
67890
Time taken: 0.16 seconds, Fetched: 1 row(s)