From: Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
To: pgsql-sql@postgresql.org
Subject: Re: [despammed] converting varchar to integer
Date: Thu, 18 Aug 2005 14:25:48 +0000 (GMT)
Message-ID: <20050818142548.48389.qmail@web52714.mail.yahoo.com> (raw)
A function to check for valid integers:
CREATE OR REPLACE FUNCTION retInt(VARCHAR) RETURNS integer AS '
DECLARE
number ALIAS FOR $1;
i INTEGER := 1;
BEGIN
IF ((number IS NULL) OR (number = '''')) THEN
RETURN NULL;
END IF;
WHILE (i <= length(number)) LOOP
IF ((substr(number,i,1) < ''0'') OR (substr(number,i,1)) > ''9'') THEN
RETURN NULL;
END IF;
i := i + 1;
END LOOP;
RETURN to_number(number,''999999999999990'');
END;
' LANGUAGE plpgsql;
\pset null (NULL)
DROP TABLE test;
CREATE TABLE test (number VARCHAR);
INSERT INTO test VALUES('123');
INSERT INTO test VALUES('a123');
INSERT INTO test VALUES('123b');
INSERT INTO test VALUES('');
SELECT retInt(number) FROM test;
retint
--------
123
(NULL)
(NULL)
(NULL)
(4 lines)
_______________________________________________________
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
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: halleypo@yahoo.com.br
Subject: Re: [despammed] converting varchar to integer
In-Reply-To: <20050818142548.48389.qmail@web52714.mail.yahoo.com>
* 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