Oracle string comparison question

Posted on 2011-10-14
Last Modified: 2012-06-27
I have two variables in the procedure code:

f1 varchar2(60);
f2 number;

I wanted to write something under IF when NOT both of f1 and f2 are not NULL:

IF  ( !( (f1 = NULL) AND (f2 = NULL) ) AND (to_number(f1) != f2) ) THEN
.....//some stuff

I get syntax error ... not sure where I am writing incorrectly..
Question by:toooki
    LVL 24

    Expert Comment

    It should just be

    IF f1 is not null and f2 is not null THEN
    END IF;

    Author Comment

    Thank you...
    But sorry I think I stated correctly...

    Can I compare f1 and f2 that are of varchar2 and number respectively?
    I go into the IF loop if f1 (converted to number) and f2 are different AND (if both are not null).

    So cases:
    f1        f2
    10       5   //goes inside IF
    4              //goes inside IF
                    //does not go inside IF
               6   //goes inside IF  
    8         8   //does not go inside IF

    LVL 24

    Accepted Solution

    You can either put all those case in separate OR clauses within the IF statement like

    IF (f1 is not null and f2 is not null and to_number(f1)!=f2) OR (f1 is not null and f2 is null) OR (f1 is null and f2 is not null) ....

    Or, if there's are a value that surely f1 and f2 will never have, you can do:

    IF to_number(nvl(f1,99999999))!=nvl(f2,99999999) THEN
    END IF;

    Author Comment

    Thanks a lot.
    IF to_number(nvl(f1,99999999))!=nvl(f2,99999999) THEN
    END IF;

    This worked for me perfectly..

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now