Oracle SQL - string not equal to

Question

I have a trigger in Oracle SQL.

CREATE OR REPLACE TRIGGER test
BEFORE INSERT ON SomeTable
FOR EACH ROW
    DECLARE str1 VARCHAR(30);
            str2 VARCHAR(30);
    BEGIN
        -- some code
        IF ( str1 <> str 2 ) THEN
            DBMS_OUTPUT.PUT_LINE( ' if ' );
        ELSE
            DBMS_OUTPUT.PUT_LINE( ' else ' );
        END IF;
    END;

Now, this always goes to the else statement, even when the strings are definitely not equal. I tried to use != instead of <> with the same result. However, it works, in reverse, if I just use

IF ( str1 = str2 ) THEN ... ELSE ... END If;

So what is the right way to test for two strings not being equal to each other (in Oracle)?

Is it possible that one of the "str" values is NULL at the time of comparison? — pilcrow
– pilcrow, Commented Nov 6, 2011 at 19:54

mu is too short · Accepted Answer · 2011-11-06 20:32:22Z

5

Can you show us the actual values being used? It is possible that the reason for the above behavior is becuase one of the values is null?

If it is possible for str1 and str2 to have null values, your if's should be like..

IF (str1 is null and str2 is null) then
   <statments depending on whether you want to treat nulls as equal>
else if (
   (str1 is null and str2 is not null) or
   (str2 is null and str1 is not null) or
   (str1 <> str2)) then
  <statements when str1 and str2 are not equal>
else
  <statements when str1 and str2 are equal?
end if;

edited Nov 6, 2011 at 20:32

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Nov 6, 2011 at 19:55

Rajesh Chamarthi

18.9k4 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pkr Over a year ago

Yes, I had thought setting the string to '' (empty string) would get rid of the null, but apparently '' is the same as null. Thanks.

pilcrow Over a year ago

(1) You have imbalanced parentheses in your else if branch; (2) you duplicate a str1/str2 criterion in the same.

mu is too short Over a year ago

@pilcrow: I'm pretty sure both of those were just typos so I fixed them. Good call pointing them out though.

pilcrow Over a year ago

FWIW, this construct might be more concisely rendered as checking for nullity, then equality, else inequality: IF (s1 IS NULL AND s2 IS NULL) THEN ... ELSE IF (s1 = s2) THEN ... ELSE ... END IF Fewer conditionals to typo. :)

vapcguy Over a year ago

@RajeshChamarthi Sorry to nitpick, but for those Googling this and finding it, I think <statements when str1 and str2 are equal? should be <statements when str1 and str2 are equal>, though all that would just get replaced by the OP's logic, anyway :)

Radim Köhler · Accepted Answer · 2013-11-06 05:13:34Z

0

This should determine if one character string exists inside another character string:

IF instr(str1,str2)<>0 THEN

edited Nov 6, 2013 at 5:13

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

answered Nov 6, 2013 at 4:56

Shyam D

172 bronze badges

Comments

Pawel Solarski · Accepted Answer · 2013-12-31 15:21:54Z

0

Sometimes it is easier to negate the equality condition. E.g. if not equal(val1, val2);

function equals(
  val1 varchar2,
  val2 varchar2
) return boolean is
begin
  if val1 is null then
    return val2 is null;
  elsif val2 is null then
    return false;
  end if;

  return val1 = val2;
end;

And the in your code you would have:

BEGIN
    -- some code
    IF NOT equals(str1, str2) THEN
        DBMS_OUTPUT.PUT_LINE( ' if ' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( ' else ' );
    END IF;
END;

answered Dec 31, 2013 at 15:21

Pawel Solarski

1,0488 silver badges7 bronze badges

Collectives™ on Stack Overflow

Oracle SQL - string not equal to

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related