How to test a variable to determine if it is all digits.

Say i've got a variable v_myvariable which is varchar(20) and equal to '01234tt567'.

In pl/pgsql, I would like to see a test that will determine if the variable is all digits.

100 points for each right answer.

-g
gmanpertAsked:
Who is Participating?
 
ivanovnCommented:
CREATE OR REPLACE FUNCTION isdigit(text) RETURNS boolean as '

-- by Ron St.Pierre (rstpierre@syscor.com)
-- licensed under the GPL
--
-- determines whether or not a value is numeric
--
-- required fields: string or number, single quoted
-- returns: true - if input is numeric, false otherwise
--
DECLARE
inputText ALIAS FOR $1;
tempChar text;
isNumeric boolean;
BEGIN
isNumeric = true;
FOR i IN 1..length(inputText) LOOP
tempChar := substr(inputText, i, 1);
IF tempChar ~ ''[0-9]'' THEN
-- do nothing
ELSE
return FALSE;
END IF;
END LOOP;
return isNumeric;
END;
' LANGUAGE 'plpgsql';

Source: http://www.thescripts.com/forum/thread174228.html
0
 
gmanpertAuthor Commented:
Yeah, and I figured out these work as well...

select '1234GG567' ~ '^[0-9]+$';
select '1234567' ~ '^[0-9]+$';

if v_var_data ~ '^[0-9]{9}$'
 then
  ... good ..
 else
  .. bad ..
 
 end if;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.