Simple BigQuery UDF to identify if a IP address is RFC1918.

bigquery_rfc1918_udf.sql

CREATE TEMP FUNCTION
  is_rfc1918(ip INT64 ) AS (
  IF
    ( ( ip BETWEEN NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("192.168.0.0"))
        AND NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("192.168.255.255") ) )
      OR ( ip BETWEEN NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("10.0.0.0"))
        AND NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("10.255.255.255") ) )
      OR ( ip BETWEEN NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("172.16.0.0"))
        AND NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING("172.31.255.255") ) ),
      TRUE,
      FALSE ) );
      
  
/* 
  Inovacation Example for testing
*/ 
WITH
  tests AS (
  SELECT
    '192.168.1.1' AS ip
  UNION ALL
  SELECT
    '172.16.100.1' AS ip
  UNION ALL
  SELECT
    '10.1.1.1' AS ip
  UNION ALL
  SELECT
    '72.14.192.2' AS ip )
SELECT
  ip,
  is_rfc1918(NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(ip))) AS rfc_1918
FROM
  tests
  
/*
Results:
[
  {
    "ip": "192.168.1.1",
    "rfc_1918": true
  },
  {
    "ip": "172.16.100.1",
    "rfc_1918": true
  },
  {
    "ip": "10.1.1.1",
    "rfc_1918": true
  },
  {
    "ip": "72.14.192.2",
    "rfc_1918": false
  }
]
*/