Fetch array elements

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Fetch array elements

Andreas Säger
Hello,

Is it possible to get a single element returned by function
REGEXP_SUBSTRING_ARRAY?

SELECT IP,
CARDINALITY(REGEXP_SUBSTRING_ARRAY( IP, '\d+')) AS "CARDINALITY"
FROM IP_LOG

For any valid IPv4 address, the above query returns 4. I'd like to
access the 1st, second, third or 4th element in SQL.

Thanks,
Andreas


------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Fetch array elements

Fred Toussi-2
You can access elements of any array with the normal syntax
select REGEXP_SUBSTRING_ARRAY( IP', '\d+')[1] ...

You may be able to use a WITH subquery to populate the array only once.
WITH SELECT IP, REGEXP_SUBSTRING_ARRAY( IP', '\d+') FROM IP_LOG AS
IPVALS(IP, IPARRAY)
SELECT IPARRAY[1], IPARRAY[2], CARDINALITY(IPARRAY) ... FROM IPVALS
 
On Mon, Dec 14, 2015, at 11:52, Andreas Säger wrote:

> Hello,
>
> Is it possible to get a single element returned by function
> REGEXP_SUBSTRING_ARRAY?
>
> SELECT IP,
> CARDINALITY(REGEXP_SUBSTRING_ARRAY( IP, '\d+')) AS "CARDINALITY"
> FROM IP_LOG
>
> For any valid IPv4 address, the above query returns 4. I'd like to
> access the 1st, second, third or 4th element in SQL.
>
> Thanks,
> Andreas
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Fetch array elements

Andreas Säger
Am 14.12.2015 um 15:18 schrieb Fred Toussi:
> You can access elements of any array with the normal syntax
> select REGEXP_SUBSTRING_ARRAY( IP', '\d+')[1] ...
>
> You may be able to use a WITH subquery to populate the array only once.
> WITH SELECT IP, REGEXP_SUBSTRING_ARRAY( IP', '\d+') FROM IP_LOG AS
> IPVALS(IP, IPARRAY)
> SELECT IPARRAY[1], IPARRAY[2], CARDINALITY(IPARRAY) ... FROM IPVALS
>  


Thank you very much. I assumed [0] based arrays all the time and never
understood what WITH can do in SQL.


------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user