Convert hex in text representation to decimal number

Question

I am trying to convert hex to decimal using PostgreSQL 9.1

with this query:

SELECT to_number('DEADBEEF', 'FMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

I get the following error:

ERROR:  invalid input syntax for type numeric: " "

What am I doing wrong?

Answer 1

You have two immediate problems:

1. to_number doesn't understand hexadecimal.
2. X doesn't have any meaning in a to_number format string and anything without a meaning apparently means "skip a character".

I don't have an authoritative justification for (2), just empirical evidence:

=> SELECT to_number('123', 'X999');
 to_number 
-----------
        23
(1 row)

=> SELECT to_number('123', 'XX999');
 to_number 
-----------
         3

The documentation mentions how double quoted patterns are supposed to behave:

In to_date, to_number, and to_timestamp, double-quoted strings skip the number of input characters contained in the string, e.g. "XX" skips two input characters.

but the behavior of non-quoted characters that are not formatting characters appears to be unspecified.

In any case, to_number isn't the right tool for converting hex to numbers, you want to say something like this:

select x'deadbeef'::int;

so perhaps this function will work better for you:

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

Then:

=> select hex_to_int('DEADBEEF');
 hex_to_int 
------------
 -559038737 **
(1 row)

** To avoid negative numbers like this from integer overflow error, use bigint instead of int to accommodate larger hex numbers (like IP addresses).