Menu
How do I compare a VARCHAR2 variable, which is an empty value?
230
Introduction to the Oracle IS NULL operator NULL is special in the sense that it is not a value like a number, character string, or datetime, therefore, you cannot compare it with any other values like zero (0) or an empty string (”).
Here is an example of how to use the Oracle IS NULL condition in a SELECT statement: SELECT * FROM suppliers WHERE supplier_name IS NULL; This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.
Use the IS [NOT] EMPTY conditions to test whether a specified nested table is empty, regardless whether any elements of the collection are NULL . The condition returns a boolean value: TRUE for an IS EMPTY condition if the collection is empty, and TRUE for an IS NOT EMPTY condition if the collection is not empty.
IS EMPTY function. The IS EMPTY function provides an alternative syntax to IS_EMPTY and also returns TRUE if that set is empty. where set is a set of any set data type, such as a multi-assign double attribute. The results of this example would the same as the previous IS_EMPTY example.
Oracle doesn't differentiate between empty strings and NULL. To check if a variable is an empty string, use the IS NULL syntax.
181
You could use either of these:
IF v_test IS NULL
THEN
-- Business Logic
or
IF NVL(v_test, 'NULL') = 'NULL'
THEN
-- Business Logic
Your question does say "compare" a VARCHAR variable which is null so if you are comparing it to another variable then:
IF (v_test1 IS NULL and v_test2 IS NULL)
THEN
-- Business Logic
That would check if they are both null.
Hope it helps...
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
