From: | Halley Pacheco de Oliveira <halleypo(at)yahoo(dot)com(dot)br> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: [despammed] converting varchar to integer |
Date: | 2005-08-18 14:25:48 |
Message-ID: | 20050818142548.48389.qmail@web52714.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Roger Tannous | 2005-08-18 14:36:22 | A Table's Primary Key Listing |
Previous Message | Christopher Browne | 2005-08-18 12:58:07 | Re: How to secure PostgreSQL Data for distribute? |