Casting hexadecimal to int - Mailing list pgsql-general

From Richard van den Berg
Subject Casting hexadecimal to int
Date
Msg-id 40b4a14a$0$36169$e4fe514c@news.xs4all.nl
Whole thread Raw
List 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         |

pgsql-general by date:

Previous
From: bgraetz@bigpond.net.au (Barry)
Date:
Subject: Re: Poatgresql database on more than one disk
Next
From: "NTPT"
Date:
Subject: FTP daemon that support Postgresql