Re: bitwise logic
Hi, You can actually unpack them using some fairly cryptic stuff. I would only use this to unpack them once to re-store them as INT. Here's an example: CREATE TABLE ip (packed CHAR(4)); INSERT INTO ip (packed) VALUES (0xB16212C); mysql SELECT * FROM ip; ++ | packed | ++ | !, | ++ 1 row in set (0.00 sec) mysql SELECT - INET_NTOA( - (ord(substring(packed, 1, 1)) 24) + - (ord(substring(packed, 2, 2)) 16) + - (ord(substring(packed, 3, 3)) 8) + - (ord(substring(packed, 4, 4))) - ) AS unpacked - FROM ip; +-+ | unpacked| +-+ | 11.22.33.44 | +-+ 1 row in set (0.00 sec) Regards, Jeremy Baron Schwartz wrote: I think Gerald was suggesting you convert the IP's into integers and then do bitwise stuff on them. I don't know of a way to do what you're asking. Baron Wagner, Chris (GEAE, CBTS) wrote: Those functions concern dotted quad IP addresses, not packed binaries. Anybody know of some obscure MySQL functions to do bit logic on strings or get MySQL to recognize a char sequence as an integer? Gerald L. Clark wrote: Wagner, Chris (GEAE, CBTS) wrote: Hi. I've got IP addresses stored in the database as packed binaries. i.e. binary(4). I put them in there that way so that I could do bitwise logic on them to do subnet searches. e.g. ...WHERE `ip` 'mask' = 'network'. Only it turns out that as far as I can tell MySQL can't do bit logic on strings, only integers. I came up with an onerous SQL call that eats the 4 bytes and makes an integer out of them but there's got to be a better way. The worst case is that I redo the database to have all the IP's as integers. Anybody know of a way to make MySQL either do the bit logic on the string or convert/cast the string as an integer? Thanks. Try INET_ATON() and INET_NTOA(). -- Gerald L. Clark Supplier Systems Corporation -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bitwise logic
Those functions concern dotted quad IP addresses, not packed binaries. Anybody know of some obscure MySQL functions to do bit logic on strings or get MySQL to recognize a char sequence as an integer? Gerald L. Clark wrote: Wagner, Chris (GEAE, CBTS) wrote: Hi. I've got IP addresses stored in the database as packed binaries. i.e. binary(4). I put them in there that way so that I could do bitwise logic on them to do subnet searches. e.g. ...WHERE `ip` 'mask' = 'network'. Only it turns out that as far as I can tell MySQL can't do bit logic on strings, only integers. I came up with an onerous SQL call that eats the 4 bytes and makes an integer out of them but there's got to be a better way. The worst case is that I redo the database to have all the IP's as integers. Anybody know of a way to make MySQL either do the bit logic on the string or convert/cast the string as an integer? Thanks. Try INET_ATON() and INET_NTOA(). -- Gerald L. Clark Supplier Systems Corporation -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bitwise logic
I think Gerald was suggesting you convert the IP's into integers and then do bitwise stuff on them. I don't know of a way to do what you're asking. Baron Wagner, Chris (GEAE, CBTS) wrote: Those functions concern dotted quad IP addresses, not packed binaries. Anybody know of some obscure MySQL functions to do bit logic on strings or get MySQL to recognize a char sequence as an integer? Gerald L. Clark wrote: Wagner, Chris (GEAE, CBTS) wrote: Hi. I've got IP addresses stored in the database as packed binaries. i.e. binary(4). I put them in there that way so that I could do bitwise logic on them to do subnet searches. e.g. ...WHERE `ip` 'mask' = 'network'. Only it turns out that as far as I can tell MySQL can't do bit logic on strings, only integers. I came up with an onerous SQL call that eats the 4 bytes and makes an integer out of them but there's got to be a better way. The worst case is that I redo the database to have all the IP's as integers. Anybody know of a way to make MySQL either do the bit logic on the string or convert/cast the string as an integer? Thanks. Try INET_ATON() and INET_NTOA(). -- Gerald L. Clark Supplier Systems Corporation -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bitwise logic
Hi. I've got IP addresses stored in the database as packed binaries. i.e. binary(4). I put them in there that way so that I could do bitwise logic on them to do subnet searches. e.g. ...WHERE `ip` 'mask' = 'network'. Only it turns out that as far as I can tell MySQL can't do bit logic on strings, only integers. I came up with an onerous SQL call that eats the 4 bytes and makes an integer out of them but there's got to be a better way. The worst case is that I redo the database to have all the IP's as integers. Anybody know of a way to make MySQL either do the bit logic on the string or convert/cast the string as an integer? Thanks. -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
