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

[#1]

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

,

[#2]

@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.

[#3]

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.

[#4]

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