I forgot to do reply all, sorry peter for the duplicate: Thank you for the responses to my question, however with a little poking around after the suggestions, I still am unable to do the conversion.
mysql> select 0xABCDEF0123456789 into @fdata; Query OK, 1 row affected (0.00 sec) mysql> select hex(@fdata); +------------------+ | hex(@fdata) | +------------------+ | ABCDEF0123456789 | +------------------+ 1 row in set (0.00 sec) mysql> select cast(@fdata AS UNSIGNED); +--------------------------+ | cast(@fdata AS UNSIGNED) | +--------------------------+ | 0 | +--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> Essentially what I am trying to accomplish is I have a BLOB column that can have arbitrarily large data, and I am using SUBSTR to pull out sections of it, and some of it needs to be masked and bit shifted. I can pull out the data I'm interested in, however to do the masking and bit shifting it appears I need it to be an integer of some kind. So I am taking blob data and trying to convert it to an integer type (unsigned) so I can do those bitwise operations. So I am putting the data into a variable like: SELECT SUBSTR(BINARY(blob_col),offset,length)) into fdata; //example This works fine. I can view this data. The problem I am having is I can't convert it to UNSIGNED to do some further bitwise operations on the data. Thanks for any further help. > Gordon, > >>... >>SELECT CONVERT(fdata,BIGINT) INTO tmp_int; >>SELECT HEX(tmp_int); > ... > > Are you looking for... > > SELECT CAST(0xABCDEF0123456789 AS UNSIGNED); > +--------------------------------------+ > | CAST(0xABCDEF0123456789 AS UNSIGNED) | > +--------------------------------------+ > | 12379813738877118345 | > +--------------------------------------+ > SELECT HEX(12379813738877118345); > +---------------------------+ > | HEX(12379813738877118345) | > +---------------------------+ > | ABCDEF0123456789 | > +---------------------------+ > > PB > > ----- > > Gordon Bruce wrote: >> I just added a user variable @fdata to get visabilility outside of the >> procedure and this is what I get. >> >> mysql> delimiter // >> mysql> create procedure test20 () >> -> BEGIN >> -> DECLARE fdata BLOB; >> -> DECLARE foffset INT UNSIGNED; >> -> DECLARE flength INT UNSIGNED; >> -> DECLARE tmp_int BIGINT UNSIGNED; >> -> >> -> SELECT 0xABCDEF0123456789 INTO fdata; >> -> SELECT 14 INTO foffset; >> -> SELECT 7 INTO flength; >> -> >> -> SELECT SUBSTR(BINARY(fdata), >> -> FLOOR(foffset/8)+1, >> -> CEIL((flength + (foffset %8 ))%8)) >> -> INTO fdata; >> -> set @fdata:=fdata; >> -> END// >> Query OK, 0 rows affected (0.00 sec) >> >> mysql> >> mysql> delimiter ; >> mysql> >> mysql> call test20(); >> Query OK, 0 rows affected (0.00 sec) >> >> mysql> >> mysql> select @fdata, hex(@fdata) >> -> >> -> ; >> +--------+-------------+ >> | @fdata | hex(@fdata) | >> +--------+-------------+ >> | ââ©âº#E | CDEF012345 | >> +--------+-------------+ >> 1 row in set (0.00 sec) >> >> -----Original Message----- >> From: David Godsey [mailto:[EMAIL PROTECTED] >> Sent: Thursday, January 19, 2006 3:33 PM >> To: mysql@lists.mysql.com >> Subject: [SPAM] - convert help - Bayesian Filter detected spam >> >> >> I am trying to convert binary data to a bigint so I can do bitwise >> operations on the data, and I'm having trouble doing it. >> >> I noticed that if I have binary data and I: >> select data>>1; I get 0 (not what I'm expecting). >> >> Here is a test procedure I wrote: >> >> create procedure test20 () >> BEGIN >> DECLARE fdata BLOB; >> DECLARE foffset INT UNSIGNED; >> DECLARE flength INT UNSIGNED; >> DECLARE tmp_int BIGINT UNSIGNED; >> >> SELECT 0xABCDEF0123456789 INTO fdata; >> SELECT 14 INTO foffset; >> SELECT 7 INTO flength; >> >> SELECT SUBSTR(BINARY(fdata), >> FLOOR(foffset/8)+1, >> CEIL((flength + (foffset %8 ))%8)) >> INTO fdata; >> >> SELECT HEX(fdata); >> SELECT CONVERT(fdata,BIGINT) INTO tmp_int; >> SELECT HEX(tmp_int); >> END >> The last two selects are added to show what I would like to do, but have >> not been able to get it to work. >> >> Any help would be great. Thanks in advance. >> >> Accomplishing the impossible means only that the boss will add it to >> your >> regular duties. >> >> David Godsey >> >> Accomplishing the impossible means only that the boss will add it to >> your >> regular duties. >> >> David Godsey >> >> >> >> ------------------------------------------------------------------------ >> >> No virus found in this incoming message. >> Checked by AVG Free Edition. >> Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: >> 1/19/2006 >> > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
