Convert a hex value stored as a varchar in an integer column to it's integer equivilent

Question

Hi: I'm working with a SQLite (v 3.14.2) in Linux. Apparently, SQLite allows users to store char strings in integer columns (I find this shocking, but that's what it apparently allows). Some of those values appear to be hex expressions of an integer. I need to clean this up in a .dump (with .mode insert). Here's an example of the odd integer/varchar behavior...

sqlite> .mode insert
sqlite> create table foo (col1 integer);
sqlite> insert into foo (col1) values (1),('2'),(0x3),('0x4'),(0xf),('My good dog Moby');
sqlite> select * from foo;
INSERT INTO table(col1) VALUES(1);
INSERT INTO table(col1) VALUES(2);
INSERT INTO table(col1) VALUES(3);
INSERT INTO table(col1) VALUES('0x4');
INSERT INTO table(col1) VALUES(15);
INSERT INTO table(col1) VALUES('My good dog Moby');

If the inserted value is an int, it gets interpreted as an int, even if it's inside single quotes. That's fine, other DBs do this too. If the value is a hex value and lacks the single quotes, that gets interpreted correctly too. So far, so good. But if the hex value is inside the single quotes, no good, it apparently gets stored as a string of some sort.

Without necessarily knowing which columns of which tables are integers that need special treatment, is there a way to get the select command to interpret hex values that were inserted with single quotes as ints (just the way it interpreted '2' as 2 above) ?

If it helps at all, I'm actually going to be using .dump, not select, when looking at the data.

Since version 3.38 (2022) there is a fairly simple solution for this, see my answer. — Wolf
– Wolf, Commented Jul 26 at 17:22
I's suggest to upgrade to a recent SQLite and use STRICT tables. — Wolf
– Wolf, Commented Jul 26 at 18:13

Wolf · Accepted Answer · 2025-07-26 17:19:24Z

3

You can actually convert from a string holding a base-16 number to a numeric value in plain Sqlite, but it's kind of ugly, using a recursive CTE:

WITH RECURSIVE
  hexnumbers(hexstr) AS (VALUES ('0x123'), ('0x4'), ('0x7f')),
  parse_hex(num, hexstr, digit, remaining) AS
    (SELECT 0, upper(hexstr), 3, length(hexstr) - 2 FROM hexnumbers
    UNION ALL
     SELECT (num * 16)
          + CASE substr(hexstr, digit, 1)
              WHEN '0' THEN 0
              WHEN '1' THEN 1
              WHEN '2' THEN 2
              WHEN '3' THEN 3
              WHEN '4' THEN 4
              WHEN '5' THEN 5
              WHEN '6' THEN 6
              WHEN '7' THEN 7
              WHEN '8' THEN 8
              WHEN '9' THEN 9
              WHEN 'A' THEN 0xA
              WHEN 'B' THEN 0xB
              WHEN 'C' THEN 0xC
              WHEN 'D' THEN 0xD
              WHEN 'E' THEN 0xE
              WHEN 'F' THEN 0xF
            END, hexstr, digit + 1, remaining - 1
      FROM parse_hex
      WHERE remaining > 0)
SELECT hexstr, num FROM parse_hex WHERE remaining = 0;

gives

hexstr  num
------  ---
0X4     4
0X7F    127
0X123   291

If you want to go the C function route, your implementation function should look something like:

void hex_to_dec(sqlite3_context *context, int argc, sqlite3_value **argv) {
    int vtype = sqlite3_value_numeric_type(argv[0]);
    if (vtype == SQLITE_TEXT)
        const char *str = sqlite3_value_text(argv[0]);
        if (str && *str) {
            sqlite3_result_int64(context, strtol(str, NULL, 16));
        }
    } else if (vtype == SQLITE_INTEGER) {
         sqlite3_result_value(context, argv[0]);
    }
}

except maybe with more error checking. The one in @choroba's answer creates a blob, not an integer, which isn't want you want.

edited Jul 26 at 17:19

Wolf

10.3k8 gold badges72 silver badges117 bronze badges

answered May 26, 2022 at 16:45

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spastika Over a year ago

Thanks for this - it allowed me to dodge a bullet. I decided to not store bitwise data in 0x### notation, but rather just stick with good old integers.

Wolf Jul 26 at 17:20

You might be interested in the update (good news?) my answer provides.

choroba · Accepted Answer · 2022-05-26 15:49:38Z

0

SQLite doesn't convert from hex to dec, you need to write such a function yourself. An example can be found in the SQLite Forum:

/*
** Function UNHEX(arg) -> blob
**
** Decodes the arg which must be an even number of hexidecimal characters into a blob and returns the blob
**
*/

#ifdef __cplusplus
extern "C" {
#endif

#ifndef SQLITE_PRIVATE
    #define SQLITE_PRIVATE static
#endif

#include <stdlib.h>
#include <string.h>

#ifdef SQLITE_CORE
    #include "sqlite3.h"
#else
    #ifdef _HAVE_SQLITE_CONFIG_H
        #include "config.h"
    #endif
    #include "sqlite3ext.h"
    SQLITE_EXTENSION_INIT1
#endif

#ifndef _WIN32_WINNT
#define _WIN32_WINNT 0x0600
#endif

static void _unhexFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    long olength = sqlite3_value_bytes(argv[0]);
    long length;
    unsigned char* data = (unsigned char*)sqlite3_value_text(argv[0]);
    unsigned char* blob;
    unsigned char* stuff;
    unsigned char buffer[4] = {0};

    if ((olength % 2 != 0) || (olength < 2))
    {
        return;
    }

    blob = malloc(length / 2);
    stuff = blob;
    length = olength;

    while (length > 0)
    {
        memcpy(buffer, data, 2);
        *stuff = (unsigned char)strtol(buffer, NULL, 16);
        stuff++;
        data += 2;
        length -= 2;
    }
    sqlite3_result_blob(context, blob, olength/2, SQLITE_TRANSIENT);
    free(blob);
}

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_sqlunhex_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);

    return sqlite3_create_function(db, "UNHEX", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, 0, _unhexFunc,  0, 0);
}

#ifdef __cplusplus
}
#endif

You can then call it on the value if it starts with 0x:

SELECT CASE
    WHEN col1 LIKE '0x%'
        THEN UNHEX(col1)
    ELSE
        col1
    END
FROM foo;

Much better way is to fix this in the application that stores the values.

answered May 26, 2022 at 15:49

choroba

245k27 gold badges221 silver badges304 bronze badges

3 Comments

daveg Over a year ago

"SQLite doesn't convert from hex to dec". You sure about that ? In the example I gave above, it seemed to convert 0x3 to 3 and 0xf to 15

Shawn Over a year ago

@daveg Literals, yes, but not in strings. See sqlite.org/lang_expr.html#hexint

Shawn Over a year ago

Oh, and that converts to a blob, not to a number... not what's needed here.

Wolf · Accepted Answer · 2025-07-26 17:18:49Z

Much simpler these days

As I learned right now in the SQLite user forum, since version 3.38.0 (2022-02-22), SQLite has JSON functions and operators integrated. This gives us a much more compact solution to the problem, namely hexstr ->> '$'.

Example

See a short demo for converting TEXT (y) and 8-byte BLOBs (z) into integers.

SQL:

WITH RECURSIVE data(x, y, z) AS (VALUES(
  1, '0x00', randomblob(8)) 
  UNION ALL SELECT 
  1+x, format('0x%02x', 1+(y->>'$')), randomblob(8)   
  FROM data WHERE x<12)
SELECT x, y, y->>'$' as "int(y)", hex(z), '0x'||hex(z)->>'$' as "int(z)" from data;

Example output (regarding the column z):

 x   y     int(y)  hex(z)            int(z)
 
 1   0x00    0     982328C6AE64A9DA  -7484093321965164070
 2   0x01    1     EF66104F92CD0F39  -1196250717068914887
 3   0x02    2     F1FAA70531C2D038  -1010311525639729096
 4   0x03    3     3816938E01984643   4041579953732994627
 5   0x04    4     775463CD9953D954   8598607323248974164
 6   0x05    5     7AB28B52E9607B9E   8841282206700698526
 7   0x06    6     0FB7761CC7A66EDA   1132503697287704282
 8   0x07    7     96CB80C4A8BE547B  -7580823965615106949
 9   0x08    8     27993C3F48A25747   2853378081415124807
10   0x09    9     40EA4542D2193B81   4677627316272708481
11   0x0a   10     F6E5E7FEC0300A4A   -655863089380455862
12   0x0b   11     2756C242B7BDDBF8   2834666607282609144

Wolf · Accepted Answer · 2025-07-26 18:12:09Z

Flexible typing

Flexible typing with "column affinity" is the default in SQLite. This might be a surprise to some:

I find this shocking, but that's what it apparently allows

The classical way to see datatypes in SQLite is flexible. If you do not take extra measures, tables work this way.

Strict typing

If you don't need to stick to such an old SQLite, STRICT tables may help preventing this, it was introduced with version 3.37.0 (2021-11-27).

Instead of declaring types just for your reference and letting do SQLite its type magic, you add the STRICT keyword after the table declaration:

sqlite> create table foo (col1 integer) strict;
sqlite> insert into foo values(6);
sqlite> insert into foo values(0x6);
sqlite> insert into foo values('0x6');
Runtime error: cannot store TEXT value in INTEGER column foo.col1 (19)
sqlite> insert into foo values('0x6'->>'$');
sqlite> select * from foo;
6
6
6

(For the syntax of the last insert, see my other answer.)

Collectives™ on Stack Overflow

Convert a hex value stored as a varchar in an integer column to it's integer equivilent

4 Answers 4

2 Comments

3 Comments

Much simpler these days

Example

Comments

Flexible typing

Strict typing

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Much simpler these days

Example

Comments

Flexible typing

Strict typing

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related