Reputation: 825
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
Upvotes: 2
Views: 419
Reputation: 1271151
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.
Upvotes: 1