SQLite Int to Hex and Hex to Int in query

Question

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.

Answer 1

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;

Answer 2

You can use sqlite3_create_function. See an example