From: | "Donald Kerr" <donald(dot)kerr(at)dkerr(dot)co(dot)uk> |
---|---|
To: | "'Josh Kupershmidt'" <schmiddy(at)gmail(dot)com> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Hex to Dec Conversion |
Date: | 2010-10-20 17:08:30 |
Message-ID: | A178A369C8334C29A1365310C41B58DD@DELLM4500 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Josh Kupershmidt
Sent: 19 October 2010 01:36
To: Donald Kerr
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Hex to Dec Conversion
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
<http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php> to use
more modern function syntax, and came up with this:
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;
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.
Josh
-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Josh Kupershmidt
Sent: 19 October 2010 01:36
To: Donald Kerr
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Hex to Dec Conversion
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
<http://archives.postgresql.org/pgsql-general/2004-05/msg00923.php> to use
more modern function syntax, and came up with this:
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;
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.
Josh
------------------------------------------------------
Thank you for your reply, Josh.
I was rather hoping not to have to call a function but it seems like it
might be the only way. I am actually using the query in a MapServer map file
and I think I may only be able to use one line of code i.e. I have to get
everything I need from the single query. I am assuming that I can add a
function to PostgreSQL so that it is available globally to any query and I
will have to do a wee bit of research to see if that is the case.
If anyone knows how to make a suggestion for inclusion in future PostgreSQL
releases then I think that a Hex_to_Int function such as
x'db_column'::integer, where db_column can be replaced by a variable, would
be a very good idea.
Many thanks once again.
Regards,
Donald
From | Date | Subject | |
---|---|---|---|
Next Message | mountain oaf | 2010-10-20 21:59:50 | "duplicate key value": version 8.4 vs 8.1 |
Previous Message | Kasia Tuszynska | 2010-10-19 20:44:31 | Re: help |