This topic provides examples of how to use NULL-related functions to identify and convert NULL values.
NULL-related functions
The characteristic of a NULL value is that no value can be equal to or unequal to NULL. You can use the IS NULL operator to check for NULL values, or use the NVL() function to convert NULL values into recognizable strings.
Examples
Create a sample table and insert appropriate data.
obclient> CREATE TABLE t_null(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(10));
Query OK, 0 rows affected
obclient> INSERT INTO t_null(id, name) VALUES(1,'A'), (2,NULL), (3,'NULL');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
Examples of using NULL-related functions in queries
Use the NVL() or IFNULL() function to identify and convert NULL values. If a column may contain NULL values, you can use the NVL() or IFNULL() function to identify them and convert them into special characters.
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