DB | Postgres | How to check if IP is in a list of IPs or a range

I have a table that has a TEXT column that holds IP, IPs or range (for example 1.1.1.1/24). In case of multiple IPs, the IPs will be separated by a @##@ for example 1.1.1.1@##@2.2.2.2

The table with 4 rows:

    ip
    ------------------
    1.1.1.1
    1.1.1.1@##@2.2.2.2
    1.1.1.1/24
    3.3.3.3
    2.2.2.2

I want to get all the rows that contain the ip 1.1.1.1 or 3.3.3.3, meaning I want to get the first 4 rows. (1.1.1.1,1.1.1.1@##@2.2.2.2,1.1.1.1/24,3.3.3.3)

I found this solution in another stack-overflow question: select inet ‘192.168.1.5’ << any (array[‘192.168.1/24′, ’10/8’]::inet[]);

but I cannot understand how can I make it work for my specific table and to get me all the first 4 rows.

Please help Thanks in advance

Answer

I think this does what you want:

select t.*
from t
where '1.1.1.1'::inet <<= any(regexp_split_to_array(t.ips, '@##@')::inet[])

Here is a db<>fiddle.

Advertisement

Answer