Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

My sql query having SUBSTRING_INDEX(string, delimiter, number)- Solution to this in hive.,Hi,

My sql query having SUBSTRING_INDEX(string, delimiter, number)- Solution to this in hive.,Hi,

New Contributor

I have one data like >>> Room no 601, Sayali Nivas , MG Road Delhi....... From this i want to pick Only 'Sayali' Based on Nivas string. How do i write query in hive for this

,

3 REPLIES 3

Re: My sql query having SUBSTRING_INDEX(string, delimiter, number)- Solution to this in hive.,Hi,

Super Guru
@Nawnath Hande

You can use Split (or) regexp_extract hive functions for this case.

1.Regexp_extract function:

hive> select trim(regexp_extract('string("Room no 601, Sayali Nivas , MG Road Delhi")', ',(.*?)(Nivas)', 1));
+---------+--+
|   _c0   |
+---------+--+
| Sayali  |
+---------+--+

2.Split Function:

hive> select trim(split(split(string("Room no 601, Sayali Nivas , MG Road Delhi"),",")[1],"Nivas")[0]);
+---------+--+
|   _c0   |
+---------+--+
| Sayali  |
+---------+--+

-

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

Re: My sql query having SUBSTRING_INDEX(string, delimiter, number)- Solution to this in hive.,Hi,

New Contributor

Hi Sir,

Thanks for giving me way but this thing i tried and not getting satisafactory results.

I want solution over My sql query having SUBSTRING_INDEX(string, delimiter, number)

this solution is not handling each and every apsect of my data. I implemented using this in mysql but right now because of more data size i want to implement in hive and i am not getting exact solution over this by these queries.

Re: My sql query having SUBSTRING_INDEX(string, delimiter, number)- Solution to this in hive.,Hi,

New Contributor

This will not satisfy - FLAT NO 601 1ST FLOOR Sayali APTT NR Tomato MARKET Rajapur PUNE 411035

Don't have an account?
Coming from Hortonworks? Activate your account here