CODEWITHSUNDEEP
sqlstringhivehiveql
buddy kumar
buddy kumar

Reputation: 21

How to extract part of a string in HIVE?

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?

Upvotes: 2

Views: 20058

Answers (3)

leftjoin
leftjoin

Reputation: 38290

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)

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use

1) a combination of substring and instr

trim(substring(val,1,instr(val,'>')-1))

2) regexp_extract

trim(regexp_extract(val,'(^.*)>',1))

Upvotes: 0

JonB65
JonB65

Reputation: 69

HIVE supports INSTR which returns basically the same function as CHARINDEX but the input field and test value are the opposite way round in the function, so I'd try

SELECT SUBSTR('ENTERPRISE > DEMO',1,INSTR('ENTERPRISE > DEMO','>') -2)

Upvotes: 0

Related Questions