Snowflake UDF: TO_INT - Convert IPv4 to its integer and IPv6 to its hexadecimal equivalent

Docs Integrations & Platforms
, , , ,
1

TO_INT converts the IPv4 IP address to its integer equivalent and IPv6 address to its hexadecimal equivalent.

TO_INT is a powerful function that can help you write custom IP address query logic and run IP address-based operations easily. The TO_INT function is used internally in our UDTF functions which makes IP address query extremely efficient.

Converting one IP address

Input IPv4 Address:

165.131.156.61

Code:

SELECT ipinfo.public.to_int('165.131.156.61') as ip_int

Output:

image

Input IPv6 Address:

a608:dd0e:5ecf:ab5c:0602:ee56:1300:42d6

Code:

SELECT ipinfo.public.to_int('a608:dd0e:5ecf:ab5c:0602:ee56:1300:42d6') as ip_hex

Ouput:

image

Converting multiple IP addresses

Input IP addresses in IPv4 and IPv6 format

ce3f:bba7:4bbf:1d90:1719:8ceb:48f7:a3b7
5f70:9e71:5cd3:305a:45bf:c590:d0d8:e975
af21:344d:1f35:d4fd:6e88:d4ff:6597:6e24
246.107.208.64
102.200.91.191
1db5:08b2:f36b:ee00:1378:452a:5ea4:9f36
b02b:9f9f:3631:44c0:e4df:0f3f:bc3d:b412
4.191.46.6
210.88.30.176
48.39.136.220

Code:

SELECT ipinfo.public.to_int(ip) as "int_hex_ip"
FROM logs

Output

image

Relevant Docs

Relevant Databases

We highly recommend users use our existing UDTFs packaged with the listing as they are the most optimized solution, but if you are familiar with database and networking logic, you can write custom functions.

:link: IPinfo Snowflake documentation

1 Like