Re: Numeric value in VARCHAR?

In article <64qjl4$98n$1_at_mark.ucdavis.edu>,   rswhitaker_at_ucdavis.edu (Robert Whitaker) wrote:
> Can anyone tell me of a way to interrogate a column that is a
> VARCHAR2 datatype to determine if the value can be converted to a
> number? Visual Basic has a nice function IsNumber(arg). Can't
> something similar be done with SQL?
> -RSW
I have included a PLL Library routine below that I've been using for a long time. I see that I wrote it before I knew about For Loops.

You might also use to_number in a block as follows. (I haven't tested this):

function Numeric(tst_item varchar2) return boolean is   tmp number;
Begin
  tmp := to_number(tst_item);
  return true;
Exception
  return false;
End;

Steve Cosner
http://members.aol.com/stevec5088

FUNCTION PLL_NUMERIC

-----------------------------------------------------------------------*
-- PLL_NUMERIC is a function to test whether a field is numeric or not.
-- It returns TRUE if the item is numeric, and false if not.  It only
-- allows numeric digits.  Plus, minus, comma and decimal are not
-- allowed.
-- It can be used several ways:
--   1. With one parameter--the item to test.  For example:
--       IF PLL_NUMERIC(:BLK.ITEM) THEN MESSAGE('ITS NUMERIC');
--   2. With 2 parameters--the item to test, and its length.  You
--      only need to pass the length parameter if the item MUST be
--      a specific length, and it is possible that the item to test
--      may not be the mandatory length.  If you don't pass the length
--      then this process checks all characters in the item.
--      Example:  IF PLL_NUMERIC(:BLK.ITEM,4) THEN
--                  Message('The item is exactly 4 numeric characters');
--   3. With 4 parameters:  #3 and #4 are the low and high value range
--      which is valid for the number.  If you do not pass these
--      parameters, the range test is not done.  Examples:
--         IF PLL_NUMERIC(TMP_VAR,NULL,0,100) THEN...--Any length is ok,
--                                   but value must be between 0 and 100
--         IF PLL_NUMERIC(TMP_VAR,4,1000,1999) THEN...--Must be 4 digits

--
-----------------------------------------------------------------------*
  (TST_ITEM VARCHAR2,
   LENGTH_I NUMBER DEFAULT NULL,
   LO_VAL   NUMBER DEFAULT NULL,
   HI_VAL   NUMBER DEFAULT NULL)
 RETURN BOOLEAN IS
   LENGTH_TMP NUMBER;
   I          NUMBER;
   TRU_FALSE  BOOLEAN := FALSE;
BEGIN
  I := LENGTH(TST_ITEM);
  LENGTH_TMP := NVL(LENGTH_I,I);
  IF  TST_ITEM IS NOT NULL
  AND I = LENGTH_TMP THEN
    I := 1;
    LOOP
      IF SUBSTR(TST_ITEM,I,1) NOT BETWEEN '0' AND '9' THEN
        EXIT;
      END IF;
      IF I < LENGTH_TMP THEN
        I := I + 1;
      ELSE
        TRU_FALSE := TRUE;
        EXIT;
      END IF;
    END LOOP;
  END IF;
  IF  TRU_FALSE
  AND LO_VAL IS NOT NULL AND HI_VAL IS NOT NULL
  AND TO_NUMBER(TST_ITEM) NOT BETWEEN LO_VAL AND HI_VAL THEN
    TRU_FALSE := FALSE;
  END IF;
  RETURN TRU_FALSE;
END;

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet