I have a table PrivateIPAddress with the following data

I need to find if an IP address is external or internal. To find an IP address as internal, the IP address that I give as an input should fall within any of the higher and lower bounds given in the table PrivateIPAddress. ie., 10.70.10.1 falls within the higher and lower bounds (ipAddress and ipAddressTo) of first row in the table.

I tried different queries, but none of them worked.

SELECT (INET_ATON('10.70.10.1') >= ANY (select inet_aton(ipAddress) from PrivateIPAddress) OR INET_ATON('10.70.10.1') <= ANY (select inet_aton(ipAddressTo) from PrivateIPAddress));

The above query will not check for each row seperately, which is why it is failing. The inet_aton function converts the IP address to an integer which helps me in checking if it is within an ip range.

Can anyone suggest a better alternative to get the required result ?

CodePudding user response：

Try this:

select case count(*) 
when 1 then 'private address'
when 0 then 'public address'
end as 'type of address' 
from  PrivateIPAddress where inet_aton('172.16.3.1') between inet_aton(ipAddress) and inet_aton(ipAddressTo);
-- result: private address

select case count(*) 
when 1 then 'private address'
when 0 then 'public address'
end as 'type of address' 
from  PrivateIPAddress where inet_aton('162.16.3.1') between inet_aton(ipAddress) and inet_aton(ipAddressTo);
-- result: public address