Re: [despammed] converting varchar to integer

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/