CODEWITHSUNDEEP
c++visual-c++sqlite
Dan
Dan

Reputation: 2755

SQLite Int to Hex and Hex to Int in query

I have some numeric data that is given to me in INTEGER form. I insert it along with other data into SQLite but when I write it out The INTEGER Numbers need to be 8 digit Hex Numbers with leading zeros.

Ex.

Input

400 
800 
25 
76

Output

00000190 
00000320 
00000019 
0000004C

Originally I was converting them as I read them in and storing them as TEXT like this.

stringstream temp;
temp << right << setw(8) << setfill('0') << hex << uppercase << VALUE;

But life is never easy and now I have to create a second output in INTEGER form not HEX. Is there a way to convert INTEGER numbers to HEX or HEX numbers to INTEGERS in SQLite?

I'd like to avoid using C++ to change data after it is in SQLite because I've written a few convent export functions that take a queries result and print them to a file. If I need to touch the data during the queries return I couldn't use them.

I've looked at the HEX() function in SQLite but that didn't have desired results. Could I make a function or would that be very inefficient? I'm doing this over a really big data set so anything expensive should be avoided.

Note: I'm using SQLites C/C++ interface with Visual Studios 2010.

Upvotes: 9

Views: 13999

Answers (3)

BigTick
BigTick

Reputation: 241

You can use sqlite3 printf() function:

CREATE TABLE T (V integer);
insert into T(V) values(400), (800), (25), (76);
select printf('%08X', V) from T;

Upvotes: 24

cprogrammer
cprogrammer

Reputation: 5675

You can use sqlite3_create_function. See an example

Upvotes: 2

Dan
Dan

Reputation: 2755

Well not my favorite answer I've decided to add a column in the table that is the INTEGER value.

If someone finds a better way to do this I'm all ears.

EDIT:

After Implementing this answer and looking at it's effect on the program this appears to be a really good way to get the data without adding much to the run time load. This answer does add to the size of the Database a little but it doesn't require any extra processing to get the values from SQLite because this is just grabbing a different column in the query.

Also because I had these values to start with, this answer had a HUGE cost savings by adding them to the table verses processing later to get values I through away earlier in the program.

Upvotes: 1

Related Questions