Inet_ntoa over a whole coloumn of a database

Question

I have a table (titled: sessions) in a database whose 1st column contain numeric ip addresses like:

_____________
| srcIP     |
-------------
| 167973143 |
| 167973187 |
| 167971450 |
-------------

To display IP addresses i am using following query

mysql> select srcIP from sessions;

I should use INET_NTOA to get the dotted ip addresses but what should be the query for displaying the whole column with dotted ip addresses?

Answer 1

Found this for example: inet_ntoa() - Convert IP addresses from a dots-and-number string to a struct in_addr and back

What you need is a function that converts the converted of IP-addresses back to the dotted form. which function you will need for this depends on the programming language you use - it CAN NOT BE DONE IN SQL itself!

In PHP you'll probably want to use long2ip()...

Answer 2

SELECT INET_NTOA(srcIP) FROM sessions

see INET_NTOA(expr) and the corresponding INET_ATON(expr)

Answer 3

Stefan Gehrig's answer is obviously better in MySQL, but in general, you can use:

select cast((srcIp/16777216) & 255 as varchar) + '.'
    + cast((srcIp/65536) & 255 as varchar) + '.'
    + cast((srcIp/256) & 255 as varchar) + '.'
    + cast(srcIp & 255 as varchar)
from sessions

to convert from a number to an IPv4 address.