×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

check column is numeric or not

check column is numeric or not

check column is numeric or not

(OP)
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.


[code]
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]

RE: check column is numeric or not

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:

CODE

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:

CODE

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):

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close