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/