Function For Number Handling

Description: This ‘AndNotNum’ Function is intended to check a string or number to see if it contains one nested element but does not contain a second nested element.

CREATE OR REPLACE FUNCTION  AndNotNum (
evalnum NUMBER,
num1in NUMBER,
num2in NUMBER)
RETURN NUMBER IS
 
evalstr VARCHAR2(38);
num1str VARCHAR2(38);
num2str VARCHAR2(38);
 
NoGood  EXCEPTION;
 
BEGIN
   evalstr := TO_CHAR(evalnum);
   num1str := TO_CHAR(num1in);
   num2str := TO_CHAR(num2in);
 
   IF INSTR(evalstr, num1str, 1, 1) = 0 THEN
      RAISE NoGood;
   END IF;
 
   IF INSTR(evalstr, num2str, 1, 1) > 0 THEN
      RAISE NoGood;
   END IF;
 
   RETURN 1;
 
EXCEPTION
   WHEN NoGood THEN
      RETURN 0;
 
END AndNotNum;
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.