Skip site navigation (1) Skip section navigation (2)

Re: Hex to Dec Conversion

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Hex to Dec Conversion
Date: 2010-10-19 00:36:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Mon, Oct 18, 2010 at 5:47 PM, Donald Kerr <donald(dot)kerr(at)dkerr(dot)co(dot)uk> wrote:
> My first post to the mailing list and I hope I am in the right place!
> I am trying to convert from hex to decimal and can do that successfully
> using the following code:
> SELECT x'FF'::integer;
> which outputs 255 and is exactly what I want.
> I want to substitute the string in the code 'FF' for a column in the
> database like so:
> SELECT x'db_column'::integer FROM db_table;
> but no matter the combinations I try, I cannot get it to work.
> Thew data colum contains html color codes like "0099FF" and I want to
> convert these to, in this case, "0 153 255".
> The following code behaves well:
> SELECT  x'00'::integer || ' ' || x'99'::integer || ' ' ||  x'FF'::integer;
> resulting in "0 153 255". All correct
> I was hopeful that something similar to the following would work but I just
> cannot get it to work despite trying various combinations.
> SELECT x'substring(col,1,2)'::integer || ' ' ||
> x'substring(col,3,2)'::integer || ' ' || x'substring(col,5,2)'::integer
> I would much prefer to do this as part of the query rather than having to
> create a function. There must be a way! :)
> Any help would be very greatly apprecaited.

Hrmph. I took a look at this and couldn't figure out how to make it
work without declaring a PL/pgSQL function, so that I could construct
a dynamic query using EXECUTE. I googled around a bit, and found a few
people having roughly your same problem: I think this comes from
Postgres not having a counterpart to its built-in to_hex() function.

Anyway, I adapted the function from here
<> to
use more modern function syntax, and came up with this:

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$
   result  int;
 EXECUTE 'SELECT x''' || hexval || '''::int' INTO result;
 RETURN result;

which you should be able to use like this:

SELECT hex_to_int(substring(color,1,2)) AS first,
               hex_to_int(substring(color,3,2)) AS second,
               hex_to_int(substring(color, 5,2)) AS third
FROM colors;

 first | second | third
     0 |    153 |   255
(1 row)

I know it's not exactly what you were looking for, but IMO the
cleanest way to do this anyway would be to make a wrapper function
like "html_color_to_int()" that would handle all this conversion for
you in one place.


In response to


pgsql-novice by date

Next:From: rezgui moniaDate: 2010-10-19 09:10:46
Subject: help
Previous:From: jrDate: 2010-10-18 23:54:17
Subject: Re: Overriding local encoding defaults

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group