How to extract part of a string in HIVE?

Question

I am looking to do this in Hive. The following is a query that works in sql server.

select LEFT('ENTERPRISE > DEMO', CHARINDEX('>', 'ENTERPRISE > DEMO') - 2)

Basically I want to extract all characters in my string to the left of >. Also, the number of characters that can appear before this > sign is variable. There is no LEFT function in hive. How can I use substr or regexp_extract for this?

Solution

Also in addition to already provided solutions with regexp_extract and substr()+instr(), you can use split():

hive> select split('ENTERPRISE > DEMO','>')[0];
OK
ENTERPRISE
Time taken: 0.099 seconds, Fetched: 1 row(s)
hive> select split('ENTERPRISE > DEMO','>')[1];
OK
 DEMO
Time taken: 0.072 seconds, Fetched: 1 row(s)

Apply trim() if you want to remove spaces:

hive> select trim(split('ENTERPRISE > DEMO','>')[0]);
OK
ENTERPRISE

Or you can split by any number of spaces+'>'+any number of spaces. split() uses regexp, no necessary to use trim():

hive> select trim(split('ENTERPRISE >DEMO',' *?> *?')[0]);
OK
ENTERPRISE
Time taken: 0.075 seconds, Fetched: 1 row(s)

Comments

[buddy kumar]

Thank you for this!!! The trim/split worked better and helped removes some issues that I was getting with spaces.

[leftjoin]

@buddykumar If you are satisfied with my answer please accept the answer