quick PL\SQL question

Hi Guys,
I need to write a PL\SQL function that would take a string input and tell me if it contains just numbers. It should return true if just numbers (negative ok) and false if its not a number... pretty easy not no clue..

Thanks!
maxbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
Create or Replace Function Is_Number(n varchar2)
Is
V_test Number;
Begin
  V_Test:=n;
  Return 1;
Exceptions
  When INVALID_NUMBER Then
    Return 0;
End;
/
0
maxbAuthor Commented:
can you walk me through that? comments?
0
MikeOM_DBACommented:
Ooops need o add:

Create or Replace Function Is_Number(n varchar2)
Return Number Is
V_test Number;
Begin
  V_Test:=n;
  Return 1;
Exceptions
  When INVALID_NUMBER Then
    Return 0;
End;
/
Where 1 = TRUE and 0 = FALSE.



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

oratimCommented:
create or replace function isNumeric(string IN varchar2)
return boolean
as
temp number;

begin
       temp:=to_number(string);
       return true;
exception
            when others then
            dbms_output.put_line(sqlerrm);
            return false;
end;

To test it:

declare
testa varchar2(100) := 'jklfsa';
testb varchar2(100) := '1234';
res boolean;

begin
       res := isNumeric(testa);
       if res = true then
             dbms_output.put_line('testa is a number');
       else
             dbms_output.put_line('testa is not a number');
      end if;
      
       res := isNumeric(testb);
       if res = true then
             dbms_output.put_line('testb is a number');
       else
             dbms_output.put_line('testb is not a number');
      end if;
end;

output:

testa is not a number
testb is a number


HTH
tim
0
MikeOM_DBACommented:
Ok what you actually need is to test for VALUE_ERROR:

Create or Replace Function Is_Number(n varchar2)
Return Number Is
V_test Number;
Begin
  V_Test:=n;
  Return 1;
Exception
  When VALUE_ERROR Then
    Return 0;
End;
/

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaramillCommented:
Or:

CREATE OR REPLACE FUNCTION is_a_number(p_number VARCHAR2) RETURN BOOLEAN AS

   -- VARIABLES

   l_sql_error_message VARCHAR2(500);

   l_test_number NUMBER;

BEGIN
   
   l_test_number := p_number;  
   RETURN(TRUE);
   
EXCEPTION

   WHEN VALUE_ERROR THEN
   
      RETURN(FALSE);
     
   WHEN OTHERS THEN

      l_sql_error_message := SQLERRM(SQLCODE);
      DBMS_OUTPUT.PUT_LINE(l_sql_error_message);
      RETURN(FALSE);
     
END is_a_number;
/
0
jaramillCommented:
Then test it:

DECLARE

   x BOOLEAN;
   
BEGIN

   x := is_a_number('a');

   DBMS_OUTPUT.ENABLE(5000);
   
   IF(x) THEN
      DBMS_OUTPUT.PUT_LINE('It is a number');      
   ELSE
      DBMS_OUTPUT.PUT_LINE('It is not a number');
   END IF;      

END;
/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.