check column is numeric or not

[keerthi2016]

Oracle : 9i.

I was about to check whether the field is numeric or not. I have written a oralce function to check whether it is numeric or not.

Even for null values it is returning as numeric values. Could you please let me know what is wrong in my procedure.

Input Values :
Column Name : ZIP_CODE Data Type varchar2
1. null
2. 8937
3. 4757
4. null
5. null
6. null

  create or replace procedure Main
  AS
  cursor data 
  IS
  select CHAIN_ID,INTERNAL_CONTACT, ZIP_CODE, ZIP_EXTENSION from staging;
  v_chainid NUMBER;
  v_zip NUMBER;
  v_internalcontact NUMBER;
  v_zipcode NUMBER;
  BEGIN
  for i in data loop
  select count(*) into v_chainid from chain where chain_id=i.CHAIN_ID;
  select count(*) into v_internalcontact from RSG_REPS where internal_contact=UPPER(LTRIM(RTRIM(i.INTERNAL_CONTACT)));
select IS_NUMBER(i.ZIP_CODE)INTO v_zipcode from DUAL;
  if v_zipcode=1 THEN 
  DBMS_OUTPUT.PUT_LINE ('Datas are numeric'|| v_zipcode);
  else
  DBMS_OUTPUT.PUT_LINE ('Datas are not numeric'|| v_zipcode);
  END IF;
  end loop;
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE ('Program errors while sending email due to:'||SQLERRM);   
  END;

Function :

CREATE or replace FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;

set serveroutput on
exec main

Output:
Data are numeric1
Data are numeric1
Data are numeric1
Data are numeric1
Data are numeric1


[code]

 

[ChrisHunt]

A value of NULL is numeric. It's also a valid string and a valid date and a valid interval and whatever other type you like - because it's just an empty value.

If you want to exclude NULL values, you'll have to explicitly do so within your function, which incidentally I would write like this:

CREATE OR REPLACE FUNCTION is_not_null_number(p_string IN VARCHAR2) RETURN BOOLEAN
   l_num NUMBER;
BEGIN
   IF p_string IS NULL THEN
      RETURN FALSE;
   END IF;
   l_num := TO_NUMBER(p_string);
   RETURN TRUE;
EXCEPTION
   WHEN VALUE_ERROR THEN
      RETURN FALSE
END;

Then you can call it like this:

IF is_not_null_number(i.zip_code) THEN
   DBMS_OUTPUT.PUT_LINE('It''s a number');
ELSE
   DBMS_OUTPUT.PUT_LINE('It''s not a number, or maybe it''s just NULL');
END IF;

Having said that, personally I would not write the function to mark NULLs as non-numeric. There will be times when you care about fields being null and times when you don't. The function becomes more re-usable if it simply returns whether the field is a valid number or not (which, as I said above, NULL is), and you add a check for the value not being null to your IF statement, like this (using your function):

IF is_number(i.zip_code) = 1 AND i.zip_code IS NOT NULL THEN
   DBMS_OUTPUT.PUT_LINE('It''s a number');
ELSE
   DBMS_OUTPUT.PUT_LINE('It''s not a number, or maybe it''s just NULL');
END IF;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd