Hi,
I have a varchar column, and I need to
1) check the value in it is an integer 2) get the integer value (as integer)
The problem is I can't suppose the're only correct values - ie there can be something like 'xssdkjsd', '230kdd' or
evenan empty string etc.
I've been looking through the documentation but I've found no functions doing this. Are there such functions?
I've been using to_number(...) function, but it raises an exception on an empty string.
I've written two on my own (see the functions below), but maybe there's something faster?
Tomas
---------------------------------------------------------------------
-- converts the varchar value to integer
-- the value has to be already checked using the is_integer function
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION to_integer(VARCHAR) RETURNS INTEGER AS '
DECLARE str ALIAS FOR $1;
BEGIN RETURN to_number(str,9999999999);
END;
' LANGUAGE plpgsql;
---------------------------------------------------------------------
-- checks whether the value is an integer (int4)
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_integer(VARCHAR) RETURNS BOOLEAN AS '
DECLARE str ALIAS FOR $1; pos INT8 := 0;
BEGIN -- only 0,1,...,9 (least one) IF NOT str ~* ''^[0-9]+$'' THEN RETURN false; END IF;
SELECT INTO pos to_number($1,9999999999);
-- check the boundaries IF (-2147483648 <= pos) AND (+2147483647 >= pos) THEN RETURN true; ELSE
RETURNfalse; END IF;
END;
' LANGUAGE plpgsql;
