hexadecimal conversion on select query

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

hexadecimal conversion on select query

Jungle Boogie
Hello All,

I'm curious to know if there's a better way to run a query and convert
the results to hexadecimal.

 This is what I have so far:
SELECT printf("%x %d",code,code), printf("%x", denom)  FROM sidemeters
order by denom

The data is already in decimal so I don't necessarily need to include
the extra %d, but it's nice for comparison.


Thanks for any input!

--
-------
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: hexadecimal conversion on select query

J Decker
maybe ?
https://www.sqlite.org/lang_corefunc.html

hex(X)The hex() function interprets its argument as a BLOB and returns
a string which is the upper-case hexadecimal rendering of the content
of that blob.

On Sat, Mar 12, 2016 at 12:18 PM, jungle Boogie <[hidden email]> wrote:

> Hello All,
>
> I'm curious to know if there's a better way to run a query and convert
> the results to hexadecimal.
>
>  This is what I have so far:
> SELECT printf("%x %d",code,code), printf("%x", denom)  FROM sidemeters
> order by denom
>
> The data is already in decimal so I don't necessarily need to include
> the extra %d, but it's nice for comparison.
>
>
> Thanks for any input!
>
> --
> -------
> inum: 883510009027723
> sip: [hidden email]
> xmpp: [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: hexadecimal conversion on select query

Jungle Boogie
On 12 March 2016 at 22:07, J Decker <[hidden email]> wrote:
> maybe ?
> https://www.sqlite.org/lang_corefunc.html
>
> hex(X)The hex() function interprets its argument as a BLOB and returns
> a string which is the upper-case hexadecimal rendering of the content
> of that blob.

That's what I first tried but not working as I expected.

Here it is in decimal: (select code from sidemeters)
"0"
"1"
"2"
"5"
"6"
"7"
"28"
"29"
"30"
"31"

And in hex with my attempt of printf: (SELECT printf("%x",code)  FROM
sidemeters)
"0"
"1"
"2"
"5"
"6"
"7"
"1c"
"1d"
"1e"
"1f"

Here is with hex(code): (SELECT hex(code) FROM sidemeters)
"30"
"31"
"32"
"35"
"36"
"37"
"3238"
"3239"
"3330"
"3331"


Am I trying to use hex() incorrectly?

Thanks!

--
-------
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: hexadecimal conversion on select query

Keith Medcalf

On Sunday, 13 March, 2016 13:36 -07:00, jungle Boogie <[hidden email]> wrote:
> On 12 March 2016 at 22:07, J Decker <[hidden email]> wrote:

> > maybe ?
> > https://www.sqlite.org/lang_corefunc.html

> > hex(X)The hex() function interprets its argument as a BLOB and returns
> > a string which is the upper-case hexadecimal rendering of the content
> > of that blob.
 
> That's what I first tried but not working as I expected.
 

> Here it is in decimal: (select code from sidemeters)
> "0"
> "1"
> "2"
> "5"
> "6"
> "7"
> "28"
> "29"
> "30"
> "31"

Assuming that your quotes mean that the value is TEXT, not a number.
You can check this by running:

     select code, typeof(code) from sidemeters;

You will note that the printf function wants to cast the "code" to an integer, then outputs the hex representation of the integer.
The hex() function treats the item as a "blob" and converts the actual bytes stored into hex.

They are two entirely different things.


> And in hex with my attempt of printf: (SELECT printf("%x",code)  FROM
> sidemeters)
> "0"
> "1"
> "2"
> "5"
> "6"
> "7"
> "1c"
> "1d"
> "1e"
> "1f"
>
> Here is with hex(code): (SELECT hex(code) FROM sidemeters)
> "30"
> "31"
> "32"
> "35"
> "36"
> "37"
> "3238"
> "3239"
> "3330"
> "3331"
>
>
> Am I trying to use hex() incorrectly?
>
> Thanks!

So really the question is, what is the declared column affinity of the "code" column in you table definition and what is the actual type of the data stored?

Based on the results you have obtained, I would suspect that the column affinity is "integer" and you are storing either text or integer (it is immaterial which in this particular case).

In order for hex() to generate output, it "converts" the integer into a blob (text) and outputs the hexified result.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: hexadecimal conversion on select query

Jungle Boogie
Hi Keith,
On 13 March 2016 at 13:31, Keith Medcalf <[hidden email]> wrote:

> On Sunday, 13 March, 2016 13:36 -07:00, jungle Boogie <[hidden email]> wrote:
>> Here it is in decimal: (select code from sidemeters)
>> "0"
>> "1"
>> "2"
>> "5"
>> "6"
>> "7"
>> "28"
>> "29"
>> "30"
>> "31"
>
> Assuming that your quotes mean that the value is TEXT, not a number.
> You can check this by running:
>
>      select code, typeof(code) from sidemeters;

It's an integer. The quotes come from copying a sample from sqlite
manager. Sorry to have omitted that from my previous message.

>
> You will note that the printf function wants to cast the "code" to an integer, then outputs the hex representation of the integer.
> The hex() function treats the item as a "blob" and converts the actual bytes stored into hex.
>
> They are two entirely different things.
>
> So really the question is, what is the declared column affinity of the "code" column in you table definition and what is the actual type of the data stored?
>
> Based on the results you have obtained, I would suspect that the column affinity is "integer" and you are storing either text or integer (it is immaterial which in this particular case).
>
> In order for hex() to generate output, it "converts" the integer into a blob (text) and outputs the hexified result.
>
>

So does that mean it can't convert it from the decimal integer to a
hexadecimal result, like I'm doing with printf?

--
-------
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: hexadecimal conversion on select query

Cezary H. Noweta
On 2016-03-13 23:14, jungle Boogie wrote:

> Hi Keith,
> On 13 March 2016 at 13:31, Keith Medcalf <[hidden email]> wrote:
>> On Sunday, 13 March, 2016 13:36 -07:00, jungle Boogie <[hidden email]> wrote:
>>> Here it is in decimal: (select code from sidemeters)
>>> "0"
>>> "1"
>>> "2"
>>> "5"
>>> "6"
>>> "7"
>>> "28"
>>> "29"
>>> "30"
>>> "31"
>>
>> Assuming that your quotes mean that the value is TEXT, not a number.
>> You can check this by running:
>>
>>       select code, typeof(code) from sidemeters;
>
> It's an integer. The quotes come from copying a sample from sqlite
> manager. Sorry to have omitted that from my previous message.

It does not matter.

>> So really the question is, what is the declared column affinity of
>> the "code" column in you table definition and what is the actual
>> type of the data stored?

``hex'' does not make use of an affinity - it ignores an affinity of an
argument.

>> Based on the results you have obtained, I would suspect that the
>> column affinity is "integer" and you are storing either text or
>> integer (it is immaterial which in this particular case).

Column with any affinity can contain TEXT (or INTEGER disjointly)
storage. There is no affinity which cannot contain TEXT nor INTEGER.
Affinity does not matter --- whatever affinity, the results of ``hex''
will be the same.

>> In order for hex() to generate output, it "converts" the integer
>> into a blob (text) and outputs the hexified result.

> So does that mean it can't convert it from the decimal integer to a
> hexadecimal result, like I'm doing with printf?

Yes, and ``printf'' is probably the best method to achieve the desired
results.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: hexadecimal conversion on select query

LincolnBurrows
This post has NOT been accepted by the mailing list yet.
In reply to this post by Jungle Boogie
If the value to convert is a hexadecimal string (such as found in a varchar column) use:
<code>-- If the '0x' marker is present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

-- If the '0x' marker is NOT present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))</code>