IP address and IP subnet validation using SQL on Redshift

Recently I needed to validate if ip address is in a subnet using sql.

A problem that is very common and pretty easy to solve if you understand basic networking.
But how to do it with sql in redshift?

One way is to build a python udf and use existing python libraries.
The problem is that it’s very slow.
Another approach is to use sql (or sql based udf).

Redshift has a built int bitwise and operator & and we can use it to solve the problem.

Given and ip_address, subnet_address and subnet_mask strings

((split_part(ip_address,'.',1)::int & (split_part(subnet_mask,'.',1)::int)=(split_part(subnet_address,'.',1)::int)) AND 
((split_part(ip_address,'.',2)::int & (split_part(subnet_mask,'.',2)::int)=(split_part(subnet_address,'.',2)::int)) AND 
((split_part(ip_address,'.',3)::int & (split_part(subnet_mask,'.',3)::int)=(split_part(subnet_address,'.',3)::int)) AND 
((split_part(ip_address,'.',4)::int & (split_part(subnet_mask,'.',4)::int)=(split_part(subnet_address,'.',4)::int))

Of course this expression can be simplified if the subnet is known in advance.
For example for subnet_address 198.105.144.0 with subnet_mask 255.255.254.0:

((split_part(ip_address,'.',1)::int & 255)=198) AND 
((split_part(ip_address,'.',2)::int & 255)=105) AND 
((split_part(ip_address,'.',3)::int & 254)=144) AND 
((split_part(ip_address,'.',4)::int & 0)=0)

This expression can be further simplified when the subnet is known.
For value 255 in the octet, the comparison can be done without the bitwise and operator,
for value 0 in the octet, no validation is required at all.
The above expression is simplified into:

((split_part(ip_address,'.',1)::int)=198) AND 
((split_part(ip_address,'.',2)::int)=105) AND 
((split_part(ip_address,'.',3)::int & 254)=144)

The performance of this expression is very fast as opposed to python, and it’s pretty simple to use.