NULL-related functions are used to handle NULL values. A NULL value cannot be equal or unequal to null. To determine whether a value is NULL, you can use the IS NULL operator or use the NVL function to replace a NULL value with a user-defined string. The following example shows how to identify and transform a NULL value.
Example: Transforming a NULL value
In an Oracle tenant, if a column may contain NULL values, you can use the NVL or NVL2 function to detect and transform the NULL values. SQL statement:
obclient> CREATE TABLE t_null(id number NOT NULL PRIMARY KEY, name varchar(10));
Query OK, 0 rows affected (0.08 sec)
obclient> INSERT INTO t_null(id, name) values(1,'A'), (2,NULL), (3,'NULL');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT id, name, nvl(name, 'NOT APPLICABLE') n_name, IFNULL(name, 'NOT APPLICABLE') n2_name
FROM t_null;
+----+------+----------------+----------------+
| id | name | n_name | n2_name |
+----+------+----------------+----------------+
| 1 | A | A | A |
| 2 | NULL | NOT APPLICABLE | NOT APPLICABLE |
| 3 | NULL | NULL | NULL |
+----+------+----------------+----------------+
3 rows in set (0.01 sec)