SQL Convert IP Address into Binary

IP Addresses are a major building block for modern communication. They allow us to interact and access resources across the web. Therefore, it is no doubt that you will come across IP address values in your database.

In this tutorial, we will show you how you can convert an IP address value from string format into a binary representation.

NOTE: In this article, we will be using Standard SQL and Google BigQuery. Keep in mind that the function implementation in this guide may vary depending on your database vendor.

Let’s get started.

NET.IP_FROM_STRING()

This function allows us to convert an IPv4 or IPv6 address into string to binary format. The function syntax is as shown below:

NET.IP_FROM_STRING(addr_str)

The function takes the IP address as the parameter (in string format) and returns it binary format representation.

You can pass the IP address as IPv4 in the format aaa.bbb.ccc.ddd

Keep in mind that the function does not support CIDR notation.

Let us illustrate how to use this function as shown in the code below:

SELECT
  ip_addr, FORMAT("%T", NET.IP_FROM_STRING(ip_addr)) AS binary
FROM UNNEST([
  '192.168.0.12', -- IPv4
  '::ffff:c0a8:d', -- IPv6 Compressed
  '0:0:0:0:0:ffff:c0a8:000d', -- IPv6 Expanded (Shortened)
  '0000:0000:0000:0000:0000:ffff:c0a8:000d' -- IPv6 Expanded:
]) AS ip_addr;

In the code above, we use the select statement and the format function to format the specified expression as a string.

We then select each IP address value from the array using the unnest function and pass that to the net.ip_from_string().

The function will take the value and convert it into its corresponding binary representation.

The resulting output is as shown below:

ip_addr binary
192.168.0.12    "b""\xc0\xa8\x00\x0c"""
::ffff:c0a8:d   "b""\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\xa8\x00\x0d"""
0:0:0:0:0:ffff:c0a8:000d    "b""\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\xa8\x00\x0d"""
0000:0000:0000:0000:0000:ffff:c0a8:000d "b""\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\xa8\x00\x0d"""

Example 2 - Passing a NULL Value

If you pass a NULL value to the function, it will return a NULL value. An example is as shown below:

SELECT NET.IP_FROM_STRING(NULL);

The code above should return an output as shown:

f0_
null

Example 3 - Passing an Invalid Value

What happens if we pass an incorrect value to the function? For example, let us see what happens when we pass a CIDR notation to the function.

SELECT NET.IP_FROM_STRING('192.168.0.2/24') AS binary;

In the example above, we are passing a CIDR notation string to the NET.IP_FROM_STRING() function. Running the code above will return an error as shown:

NET.IP_FROM_STRING() encountered an unparseable IP-address: 192.168.0.2/24

This means that the function cannot parse the provided string format.

NET.SAFE_IP_FROM_STRING()

In some cases, you may not want to face any errors when passing an incorrect value. We can solve for that by using the NET.SAFE_IP_FROM_STRING() function.

The function performs a similar function as the NET.IP_FROM_STRING(). However, it returns NULL if the input string is incorrect.

The function syntax is as shown:

NET.SAFE_IP_FROM_STRING(addr_str)

Consider the example shown below:

SELECT NET.SAFE_IP_FROM_STRING('192.168.0.2/24') AS binary;

In this case, the function should return NULL as the input format is incorrect.

binary
NULL

NET.IP_TO_STRING

Standard SQL also allows you to convert a binary into a string IP address. It is a reverse of the NET.IP_FROM_STRING().

The function syntax is as shown below:

NET.IP_TO_STRING(addr_bin)

An example usage is as shown below:

SELECT NET.IP_TO_STRING(b"\xc0\xa8\x00\x0c") AS ip;

The query above should convert the provided binary format into a string IP address. The resulting value is as shown:

ip
192.168.0.12

Source Code

If you would like to replicate the steps in this article, the source code is provided below:

SELECT
  ip_addr, FORMAT("%T", NET.IP_FROM_STRING(ip_addr)) AS binary
FROM UNNEST([
  '192.168.0.12', -- IPv4
  '::ffff:c0a8:d', -- IPv6 Compressed
  '0:0:0:0:0:ffff:c0a8:000d', -- IPv6 Expanded (Shortened)
  '0000:0000:0000:0000:0000:ffff:c0a8:000d' -- IPv6 Expanded:
]) AS ip_addr;

SELECT NET.IP_FROM_STRING(NULL);
SELECT NET.SAFE_IP_FROM_STRING('192.168.0.2/24') AS binary;

SELECT NET.IP_TO_STRING(b"\xc0\xa8\x00\x0c") AS ip;

Conclusion

In this article, we learned how to convert an IP address from string format into it’s binary representation. We hope you enjoyed the tutorial.

Thanks for reading!!!