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

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$ (view raw, whole thread or download thread mbox)
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 '
   h alias for $1;
   exec varchar;
   curs refcursor;
   res  int;
  exec := ''SELECT x'''''' || h || ''''''::int'';
  OPEN curs FOR EXECUTE exec;
  FETCH curs INTO res;
  CLOSE curs;
  return res;
LANGUAGE 'plpgsql'

Richard van den Berg, CISSP

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

pgsql-general by date

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

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