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 <[email protected]> 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 <[email protected]> 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
>>
>>
>
