This topic provides specific examples on how to use NULL-related functions to define and replace NULL values.
NULL-related functions
A NULL value cannot be equal or unequal to any numeric value. 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.
Examples
Create a table and insert proper data into the table.
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
Use NULL-related functions in a query
Use the NVL() or IFNULL() function to define and replace NULL values. In the MySQL mode of OceanBase Database, if a column may contain NULL values, you can use the NVL() or IFNULL() function to identify NULL values and replace the NULL values with strings.
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