IP Range in SQL

Question

I have hundreds of thousands of IP addresses and you want to determine which ones are in a certain range. Ranges:

64.233.160.0 /  8192
66.102.0.0 / 4096
66.249.64.0 / 8192
72.14.192.0 / 16384
74.125.0.0  / 65536
209.85.128.0 / 32768
216.239.32.0 / 8192

So, I converted these ranges to the following:

64.233.160.0    -   64.233.192.0
66.102.0.0      -   66.102.16.0
66.249.64.0     -   66.249.96.0
72.14.192.0     -   72.15.0.0
74.125.0.0      -   74.126.0.0
209.85.128.0    -   209.86.0.0
216.239.32.0    -   216.239.64.0

So now I want to ask if the IP address is in any of these ranges. SQL is not going to understand octets, so I don't know what to do.

Can I use some Hex2Dec / Dec2Hex conversions?

I believe that this should be something that has been done before, I am sure that I am not the first person who is trying to determine a specific ip in the list using the ip range.

I will look up at several IP addresses, so some may be 20.0.1.123, and the other may be 124.123.123.1, i.e. octet format will not be the same

Answer 1

IP- :

PARSE_IP('64.233.160.0') returns 1089052672

BETWEEN.

Answer 2

IP- . , , , . , .

Answer 3

Pentium10 Legacy Bigquery. , BigSquare StandardSQL, :

NET.IP_FROM_STRING ('64.233.160.0 ') 1089052672

Answer 4

BigQuery Standard SQL IP

 

#standardSQL  
SELECT NET.IPV4_TO_INT64(NET.IP_FROM_STRING('64.233.160.0'))   

SQL UDF -

#standardSQL
CREATE TEMP FUNCTION ip2int(ip STRING) AS (
  NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip))
);
WITH Ranges AS (
  SELECT '64.233.160.0' AS IP1, '64.233.192.0' AS IP2 UNION ALL
  SELECT '66.102.0.0', '66.102.16.0' UNION ALL
  SELECT '66.249.64.0', '66.249.96.0' UNION ALL
  SELECT '72.14.192.0', '72.15.0.0' UNION ALL
  SELECT '74.125.0.0', '74.126.0.0' UNION ALL
  SELECT '209.85.128.0', '209.86.0.0' UNION ALL
  SELECT '216.239.32.0', '216.239.64.0' 
),
IPs AS (
  SELECT '64.233.160.2' AS IP UNION ALL
  SELECT '72.14.192.101'
)
SELECT *
FROM IPs AS i
JOIN Ranges AS r
ON ip2int(IP) BETWEEN ip2int(IP1) AND ip2int(IP2)  

IP              IP1             IP2  
72.14.192.101   72.14.192.0     72.15.0.0    
64.233.160.2    64.233.160.0    64.233.192.0     

More on NET functionsandSQL UDF