How the INET_NTOA() function works in Mariadb?

The MariaDB INET_NTOA() function is used to convert a numeric representation of an IPv4 address into the dotted-quad notation. This function is handy when you need to display IP addresses stored as integers in a more human-readable format.

Syntax

MariaDB INET_NTOA() function’s syntax is as follows:

INET_NTOA(numeric_ip)

Where numeric_ip is the integer value representing the IPv4 address. The function returns the IP address in dotted-quad notation.

Examples

Example 1: Basic Conversion

This example shows how to convert a numerical IP address back to its standard IPv4 format.

SELECT INET_NTOA(3232235521);

The output for this statement is:

+-----------------------+
| INET_NTOA(3232235521) |
+-----------------------+
| 192.168.0.1           |
+-----------------------+

This result is the IPv4 address equivalent to the numerical value 3232235521.

Example 2: Conversion of Zero

Demonstrating the conversion of a zero value.

SELECT INET_NTOA(0);

The output for this statement is:

+--------------+
| INET_NTOA(0) |
+--------------+
| 0.0.0.0      |
+--------------+

The zero value is converted to the IPv4 address 0.0.0.0.

Example 3: Maximum Integer Value

Showing the conversion of the maximum integer value that can represent an IPv4 address.

SELECT INET_NTOA(4294967295);

The output for this statement is:

+-----------------------+
| INET_NTOA(4294967295) |
+-----------------------+
| 255.255.255.255       |
+-----------------------+

The maximum integer value is converted to the IPv4 address 255.255.255.255.

Example 4: Using INET_NTOA() with a Table

Creating a table to demonstrate the conversion of numerical IP addresses to the dotted format.

DROP TABLE IF EXISTS numeric_ips;
CREATE TABLE numeric_ips (numeric_ip BIGINT);
INSERT INTO numeric_ips (numeric_ip) VALUES (3232235521), (167772161);

SELECT numeric_ip, INET_NTOA(numeric_ip) AS ip FROM numeric_ips;

The output for this statement is:

+------------+-------------+
| numeric_ip | ip          |
+------------+-------------+
| 3232235521 | 192.168.0.1 |
|  167772161 | 10.0.0.1    |
+------------+-------------+

This table shows the numerical IP addresses and their dotted-quad equivalents.

Example 5: Null Value

Explaining the function’s behavior when provided with a NULL value.

SELECT INET_NTOA(NULL);

The output for this statement is:

NULL

When given a NULL value, the function returns NULL.

Related Functions

Below are a few functions related to the MariaDB INET_NTOA() function:

• MariaDB INET_ATON() function is used to convert an IPv4 address in dotted-quad notation to a numerical representation.
• MariaDB INET6_ATON() function is used for converting IPv6 addresses into their numerical representation.
• MariaDB INET6_NTOA() function is used to convert a numerical representation of an IPv6 address back to its standard format.

Conclusion

The INET_NTOA() function in MariaDB is essential for converting numerical representations of IPv4 addresses back into a human-readable dotted format. It complements the INET_ATON() function and is particularly useful when retrieving and displaying IP address data from a database.