Syntax error MySQL query to copy IP address from varchar column to integer in the same table?

MySQL query to copy IP address from varchar column to integer in the same table?

For this, you can use INET_ATON(). Let us first create a −

mysql> create table DemoTable1404
   -> (
   -> IpAddress varchar(40)
   -> );
Query OK, 0 rows affected (1.02 sec)

Insert some records in the table using insert −

mysql> insert into DemoTable1404 values('192.168.120.0');
Query OK, 1 row affected (0.43 sec)
mysql> insert into DemoTable1404 values('192.168.120.20');
Query OK, 1 row affected (0.60 sec)
mysql> insert into DemoTable1404 values('224.0.0.0');
Query OK, 1 row affected (0.42 sec)

Display all records from the table using select −

mysql> select * from DemoTable1404;

This will produce the following output −

+----------------+
| IpAddress      |
+----------------+
| 192.168.120.0  |
| 192.168.120.20 |
| 224.0.0.0      |
+----------------+
3 rows in set (0.00 sec)

Following is the query to copy IP address from varchar column to integer in the same −

mysql> select inet_aton(IpAddress) from DemoTable1404;

This will produce the following output −

+----------------------+
| inet_aton(IpAddress) |
+----------------------+
|           3232266240 |
|           3232266260 |
|           3758096384 |
+----------------------+
3 rows in set (0.00 sec)

No, let us prove that the conversion was successful or not −

mysql> select inet_ntoa('3232266240');

This will produce the following output −

+-------------------------+
| inet_ntoa('3232266240') |
+-------------------------+
| 192.168.120.0           |
+-------------------------+
1 row in set (0.27 sec)
Updated on: 2019-11-11T10:55:05+05:30

253 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements