SQL data conversion help

[WorldCraft]

Hello. I have a chat set up on my forum, and bans are recorded into a table. The data recorded for bans include the IP. But the data looks like this:



I'd like to convert and record the IPs for my own personal logs. It looks like HEX, but straight conversion of HEX to string doesn't seem to return anything useful...I'm very new to MySQL data manipulation so if someone could help show me how that would be great.

For additional information, this column's data type is varbinary and is using the UNHEX function.

Thanks,
Mike

[kh99]

I think this might work:

SELECT INET_NTOA(CONV(HEX(ip), 16, 10)) FROM tablename

but I'm not sure because I don't know what the actual ip addresses are supposed to be.

[WorldCraft]

Yup! This worked fine. Thanks! :up:

I'm a newbie to SQL and trying to learn it, so bear with me :P. This function is converting the values from the ip column into hexadecimal, then converting that to base 10, then converting that into an IPv4 address. Correct?

Also, could you explain why the original values in the column do not appear as UNHEX'd? It seems that the only two that appear to be UNHEX'd are #s 2 and 9 in the column.

[kh99]

Originally Posted by WorldCraft

This function is converting the values from the ip column into hexadecimal, then converting that to base 10, then converting that into an IPv4 address. Correct?

Right. In case you haven't found it (or something similar), there's an online MySQL manual with a function reference here: http://dev.mysql.com/doc/refman/5.0/en/functions.html

Also, could you explain why the original values in the column do not appear as UNHEX'd? It seems that the only two that appear to be UNHEX'd are #s 2 and 9 in the column.

To be honest, I'm not an SQL expert or anything, but I like figuring out stuff like that. So I don't know the answer for sure, but my guess would be this: that column is just 4 bytes (one representing each section of an ip address), and whatever program you use to display that column (phpMyAdmin or whatever) displays it as a 4 character text string if all the bytes in the string happen to represent printable characters, otherwise it shows it as hexadecimal.

[WorldCraft]

Yeah, I was thinking about that too, if they had to be printable characters, since trying some online conversion resources would return some strings with some arbitrary or non-standard characters. Thanks for the info.