INSTR function

[Leon Clayton <lc...@maprtech.com>]

Hello All

Anyone come up with a way to do the instr function within Apache Drill. INSTR function returns the position of a substring in a string. 

Regards
 
Leon Clayton

Re: INSTR function

[Andries Engelbrecht <ae...@maprtech.com>]

You can use POSTION, STRPOS or LOCATE also to find substring in a string and return the position.

LOCATE is the most useful IMO as it allows you to specify where to starter the search in the string, however none of these have the INSTR or substring-index ability to specify which occurrence of the substring to look for.

A good case for a UDF.

Below is a workaround with subqueries to break down an IP address in the different portions.

select p2.ip_address, p2.part1, p2.part2, substr(p2.rest2, 1, locate('.',p2.rest2)-1) as part3,
substr(rest2, locate('.',rest2)+1) as part4
from
(select p1.ip_address, p1.part1, substr(rest1, 1, locate('.',rest1)-1) as part2, 
substr(rest1, locate('.',rest1)+1) as rest2
from
(select ip_address, substr(ip_address, 1, locate('.',ip_address)-1) as part1,
substr(ip_address, locate('.',ip_address)+1) as rest1 from `/ip`) as p1) as p2
+---------------+--------+--------+--------+--------+
|  ip_address   | part1  | part2  | part3  | part4  |
+---------------+--------+--------+--------+--------+
| 172.16.254.1  | 172    | 16     | 254    | 1      |
+---------------+--------+--------+--------+————+


—Andries


> On Sep 8, 2015, at 12:27 AM, Rajkumar Singh <rs...@maprtech.com> wrote:
> 
> you can use the Position function in drill which do the same thing.
> 
> select POSITION(’substring’ in ’string’) from sample_table
> 
> Rajkumar Singh
> MapR Technologies
> 
> 
>> On Sep 8, 2015, at 12:48 PM, Leon Clayton <lc...@maprtech.com> wrote:
>> 
>> Hello All
>> 
>> Anyone come up with a way to do the instr function within Apache Drill. INSTR function returns the position of a substring in a string. 
>> 
>> Regards
>> 
>> Leon Clayton
>> 
>> 
> 

Re: INSTR function

[Rajkumar Singh <rs...@maprtech.com>]

you can use the Position function in drill which do the same thing.

select POSITION(’substring’ in ’string’) from sample_table

Rajkumar Singh
MapR Technologies


> On Sep 8, 2015, at 12:48 PM, Leon Clayton <lc...@maprtech.com> wrote:
> 
> Hello All
> 
> Anyone come up with a way to do the instr function within Apache Drill. INSTR function returns the position of a substring in a string. 
> 
> Regards
> 
> Leon Clayton
> 
>