SQL: Binary to IP Address

binary, mysql, string, string-formatting

Solution

mysql> select inet_ntoa(conv('4333d26e', 16, 10));
+-------------------------------------+
| inet_ntoa(conv('4333d26e', 16, 10)) |
+-------------------------------------+
| 67.51.210.110                       |
+-------------------------------------+
1 row in set (0.00 sec)

Check if it works there too =)

Edit

The problem is that `inet_ntoa` seems to parse from decimal `strings` number representation, not hexadecimal ones, or from hexadecimal `integers`. Compare:

mysql> select inet_ntoa(0x4333d26e);
+-----------------------+
| inet_ntoa(0x4333d26e) |
+-----------------------+
| 67.51.210.110         |
+-----------------------+
1 row in set (0.02 sec)

mysql> select inet_ntoa('0x4333d26e');
+-------------------------+
| inet_ntoa('0x4333d26e') |
+-------------------------+
| 0.0.0.0                 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

Edit

This is simpler and seems to work too:

SELECT INET_NTOA(CONV(ip_bin, 2, 10)) FROM log_metadata

Problem

I'm trying to convert A binary IP to a human-readable IP ``` SELECT HEX( `ip_bin` ) FROM `log_metadata` ``` gives me `4333D26E000000000000000000000000` And ``` SELECT INET_NTOA(0x4333D26E) ``` gives me `67.51.210.110` So I tried: ``` SELECT SUBSTRING( CONVERT(HEX(`ip_bin`), CHAR(32)), 1, 8 ) AS `A` , INET_NTOA( SUBSTRING( CONVERT(HEX(`ip_bin`), CHAR(32)), 1, 8 ) ) AS `B` , INET_NTOA(hex(`ip_bin`)) AS `C` , INET_NTOA(`ip_bin`) AS `D` FROM `log_metadata` ``` But I only get ``` +----------+------------+------------+---------+ | A | B | C | D | +----------+------------+------------+---------+ | 4333D26E | 0.0.16.237 | 0.0.16.237 | 0.0.0.0 | +----------+------------+------------+---------+ ``` Any suggestions?

Original source