Casting hexadecimal to int

From: Richard van den Berg <richard(dot)vandenberg(at)trust-factory(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Casting hexadecimal to int
Date: 2004-05-26 13:53:14
Message-ID: 40b4a14a$0$36169$e4fe514c@news.xs4all.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I was looking for a way to cast hex numbers (stored in a varchar column)
to int. I found many people asking the same thing, but no answers.

It seems that this is supported by the following syntax

select x'1f'::int;

Since this doesn't work with variable values, I wrote this following
function. Perhaps it will help anyone wanting to do the same thing.

CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS '
DECLARE
h alias for $1;
exec varchar;
curs refcursor;
res int;
BEGIN
exec := ''SELECT x'''''' || h || ''''''::int'';
OPEN curs FOR EXECUTE exec;
FETCH curs INTO res;
CLOSE curs;
return res;
END;'
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT;

--
Richard van den Berg, CISSP

Trust Factory B.V. | http://www.trust-factory.com/
Bazarstraat 44a | Phone: +31 70 3620684
NL-2518AK The Hague | Fax : +31 70 3603009
The Netherlands |

Browse pgsql-general by date

  From Date Subject
Next Message Janez Kostanjšek 2004-05-26 14:17:17 Error: server closed the connection unexpectedely
Previous Message Robert Treat 2004-05-26 13:51:41 Re: Clustering Postgres