From: tv@fuzzy.cz
To: pgsql-sql@postgresql.org
Subject: converting varchar to integer
Date: Wed, 17 Aug 2005 01:34:48 +0200
Message-ID: <1124235288.43027818cb6e3@email.gigaweb.cz> (raw)
Hi,
I have a varchar column, and I need to
1) check the value in it is an integer
2) get the integer value (as integer)
The problem is I can't suppose the're only correct
values - ie there can be something like 'xssdkjsd',
'230kdd' or even an empty string etc.
I've been looking through the documentation but I've
found no functions doing this. Are there such functions?
I've been using to_number(...) function, but it raises
an exception on an empty string.
I've written two on my own (see the functions below),
but maybe there's something faster?
Tomas
---------------------------------------------------------------------
-- converts the varchar value to integer
-- the value has to be already checked using the is_integer function
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION to_integer(VARCHAR) RETURNS INTEGER AS '
DECLARE
str ALIAS FOR $1;
BEGIN
RETURN to_number(str,9999999999);
END;
' LANGUAGE plpgsql;
---------------------------------------------------------------------
-- checks whether the value is an integer (int4)
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_integer(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
str ALIAS FOR $1;
pos INT8 := 0;
BEGIN
-- only 0,1,...,9 (least one)
IF NOT str ~* ''^[0-9]+$'' THEN
RETURN false;
END IF;
SELECT INTO pos to_number($1,9999999999);
-- check the boundaries
IF (-2147483648 <= pos) AND (+2147483647 >= pos) THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE plpgsql;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: converting varchar to integer
In-Reply-To: <1124235288.43027818cb6e3@email.gigaweb.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox