DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=VqqQNuJFhVpZ5qUghpp5xraxPZpWzDETVU5XQRUKs8VBYoqiufELgduRVXjfiafioxTZdihvt1niVxWwd9Yf9PqI238ruae5y5MZ6GvqshaHzL/MOMkqH9ziHiEvJpn3VqiLfBWAnU+PHl2/oprtiZZjeCtBi7QaQQFZUPAfqJQ=
	;
Message-ID: <20050818142548.48389.qmail@web52714.mail.yahoo.com>
Date: Thu, 18 Aug 2005 14:25:48 +0000 (GMT)
From: Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
Subject: Re: [despammed] converting varchar to integer
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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/