PostgreSQL: Treat TEXT column as hexadecimal number

Question

I have a table that has a TEXT column which contains hexadecimal numbers. I now need to represent them as the integers that they really are, but found no way of doing that (something like to_hex() in reverse).

I am aware that I can convert literal hexadecimal values like this:

SELECT x'DEADBEEF';

But how do I apply something like this if the value to be converted comes from a column? Concatenating 'x' to the column name obviously doesn't work, because then it is no longer a string literal.

I found a very ugly function in the PostgreSQL mailing lists which pieces together a query string such that the function argument is then again a literal, and then executes that function, but the approach is just downright perverse---there has to be a better way. At least I hope so, given that the message is almost ten years old…

Of course, I know that having the value in question stored as an integer in the database in the first place would be the way to go. But this is not possible in this case, so I'm stuck with trying to decypher those strings…)

It would be easier to implement this in software code that fetches the results. Would that be a problem for some reason? — Marcelo Zabani, Jan 07 '13 at 02:44
I need to do some queries where I actually to work with the numeric value of that hex number; other than that the hex number is just used for displaying. — Vucar Timnärakrul, Jan 07 '13 at 03:12

score 1 · Answer 1 · answered Jan 07 '13 at 02:45

Hm, there might be a simpler way to do it.

CREATE FUNCTION from_hex(text) RETURNS integer AS $$
DECLARE
    x bytea;
BEGIN
    x := decode($1, 'hex');
    return (get_byte(x, 0) << 24) | (get_byte(x, 1) << 16) |
           (get_byte(x, 2) << 8) | get_byte(x, 3);
END
$$ LANGUAGE plpgsql;

Note that as written, this only works for 8-digit hexadecimal numbers.

dschulz · Accepted Answer · 2013-01-07T03:20:10.553

The following functions are roughly the same as the function in that post from the mailing list. In fact, I took them from the mailing list too, but from a newer post. I can't see anything wrong with them. I've used it just once while migrating a small dataset.

Feel free to downvote if you can point anything "perverse" that could potentially derive from its use.

With INTEGER datatype:

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$
DECLARE
  result  int;
BEGIN
EXECUTE 'SELECT x''' || hexval || '''::int' INTO result;
  RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

With BIGINT datatype:

CREATE OR REPLACE FUNCTION hex_to_bigint(hexval varchar) RETURNS bigint AS $$
DECLARE
   result  bigint;
BEGIN
EXECUTE 'SELECT x''' || hexval || '''::bigint' INTO result;
 RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

'Perverse' is maybe too strong a word; the thing that itches somewhat me is that embedded `EXECUTE` with the patched-together string. But at least this version is much more readable, so I'll settle on this one. Thanks for your effort. :o) — Vucar Timnärakrul, Jan 07 '13 at 13:20

PostgreSQL: Treat TEXT column as hexadecimal number

2 Answers2