PL/SQL - Comparison Operators
Comparison operators are used for comparing one expression to another. The result is always either TRUE, FALSE or NULL.
Operator | Description | Example |
---|---|---|
LIKE | The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not. | If 'Zara Ali' like 'Z% A_i' returns a Boolean true, whereas, 'Nuha Ali' like 'Z% A_i' returns a Boolean false. |
BETWEEN | The BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b means that x >= a and x <= b. | If x = 10 then, x between 5 and 20 returns true, x between 5 and 10 returns true, but x between 11 and 20 returns false. |
IN | The IN operator tests set membership. x IN (set) means that x is equal to any member of set. | If x = 'm' then, x in ('a', 'b', 'c') returns Boolean false but x in ('m', 'n', 'o') returns Boolean true. |
IS NULL | The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. Comparisons involving NULL values always yield NULL. | If x = 'm', then 'x is null' returns Boolean false. |
LIKE Operator
This program tests the LIKE operator. Here, we will use a small procedure() to show the functionality of the LIKE operator −
DECLARE PROCEDURE compare (value varchar2, pattern varchar2 ) is BEGIN IF value LIKE pattern THEN dbms_output.put_line ('True'); ELSE dbms_output.put_line ('False'); END IF; END; BEGIN compare('Zara Ali', 'Z%A_i'); compare('Nuha Ali', 'Z%A_i'); END; /
When the above code is executed at the SQL prompt, it produces the following result −
True False PL/SQL procedure successfully completed.
BETWEEN Operator
The following program shows the usage of the BETWEEN operator −
DECLARE x number(2) := 10; BEGIN IF (x between 5 and 20) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; IF (x BETWEEN 5 AND 10) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; IF (x BETWEEN 11 AND 20) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; END; /
When the above code is executed at the SQL prompt, it produces the following result −
True True False PL/SQL procedure successfully completed.
IN and IS NULL Operators
The following program shows the usage of IN and IS NULL operators −
ECLARE letter varchar2(1) := 'm'; BEGIN IF (letter in ('a', 'b', 'c')) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; IF (letter in ('m', 'n', 'o')) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; IF (letter is null) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; END; /
When the above code is executed at SQL prompt, it produces the following result:
False True False PL/SQL procedure successfully completed.