If you store your ip addresses as a 32 bit unsigned integer representation instead of strings(using INET_ATON()), you can use bitwise operators to pull the octets for grouping and sorting when you need to retrieve them:select ipAddress, (ipAddress >> 24) as firstOctet, (ipAddress>>16<<16)-(ipAddress>>24<<24)>>16 as secondOctet,(ipAddress>>8<<8)-(ipAddress>>16<<16)>>8 as thirdOctet,ipAddress-(ipAddress>>8<<8) as fourthOctetfrom ips;Result:

This way you don't need a string parser to separate the octets for display, drill down menus, sorting etc, and don't need to tear the octets apart with a string parser to put them in separate fields.

If these are your IP addresses I sincerely apologize. I just pulled them out of thin air to write this sql :D

There are 10 types of people in the world...Sorry had to throw that in there 8)

There are probably "easier" ways to do this, but they aren't as efficient. I had a requirement for a tree view of ip addresses and this seems to be the easiest way to do it.

-Neil

Posted by
Phillip Temple
on
September 11, 2008

Neil, you will find the following more efficient:ipAddress, (ipAddress >> 24) as firstOctet,(ipAddress>>16) & 255 as secondOctet,(ipAddress>>8) & 255 as thirdOctet,ipAddress & 255 as fourthOctetfrom ips;

Phillip.

Posted by
Ben Wiseley
on
August 7, 2009

This took me a while to figure out so, if it helps anyone...

If you want to turn on/off bit settings you do it like so, you need to do the &~ first and the | second.

Say prefs = 1636 (1024 | 512 | 64 | 32 | 4) and you want to turn off 512 and 64 and turn on 4 and not touch 1024

select prefs &~ 512 &~ 64 | 4;1060

Even if prefs is 0 - you get the same result

Just make sure to put all the &~s first

Posted by
Will Entriken
on
December 11, 2009

@ greg gerard

In your case, could use SIGN() rather than IF. Or given that you are using SUM(), even could use >>:

Seems there is a shortage of examples for bitwise logic in MySQL. Here is how I have used it:

I have a database of customers who buy services from a company. The status of the customer tells me what service(s) they use and if they are current in buying that service. So for an example lets say my services include:Pool Service, lawn mowing, window washing, tree trimming, house painting, mobile oil change, etc.. (you get the idea).

With this scheme a customer who needs pool service (4) and window washing (16) will have a status of 20 (4 + 16). A prospect (1) who might be interested in a mobile oil change (128) would have a status of 129 (1+128). etc...

Bitwise logic allows me to select customers who buy particular services based on their status value.

SELECT * FROM customers WHERE status & 1//returns all prospects)

SELECT * FROM customers WHERE status & 16//returns all of my window washing customers even if they are cancelled

SELECT * FROM customers WHERE (status & 16) AND !(status & 2)//returns all of my window washing customers but not the cancelled ones

SELECT * FROM customers WHERE status & 36//returns all of my tree trimming AND pool service customers i.e. 32+4