From: Peter Brawley Date: January 20 2006 1:24am Subject: Re: [SPAM] - convert help - Bayesian Filter detected spam List-Archive: http://lists.mysql.com/mysql/194077 Message-Id: <43D03BC6.8080005@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43D03BC636B7=======" --=======AVGMAIL-43D03BC636B7======= Content-Type: multipart/alternative; boundary=------------080804020100010607000007 --------------080804020100010607000007 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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:mysql@stripped] > Sent: Thursday, January 19, 2006 3:33 PM > To: mysql@stripped > 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 > --------------080804020100010607000007 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 
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:mysql@stripped] 
Sent: Thursday, January 19, 2006 3:33 PM
To: mysql@stripped
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
--------------080804020100010607000007-- --=======AVGMAIL-43D03BC636B7======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-43D03BC636B7=======--