If the first parameter value is not null it returns the value in the second parameter. The NVL2 function accepts three parameters. SQL> SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS output FROM null_test_tab ORDER BY id The DECODE function is not specifically for handling null values, but it can be used in a similar way to the NVL function, as shown by the following example. SQL> SELECT id, NVL(col1, 'ZERO') AS output FROM null_test_tab ORDER BY id Using the NVL function we replace the null values with 'ZERO'. We know that COL1 in the test table contains null in all rows except the first. If the first parameter is any value other than null, it is returned unchanged. If the value in the first parameter is null, the function returns the value in the second parameter. The NVL function allows you to replace null values with a default value. SQL> SELECT * FROM null_test_tab WHERE col1 IS NULL ORDER BY id Instead they must use the IS NULL or IS NOT NULL operators. SQL> SELECT * FROM null_test_tab WHERE col1 = NULL ORDER BY id Remember, comparisons against null always result in null, so queries can't use regular comparison operators like '=' or '!='. SQL> SELECT * FROM null_test_tab ORDER BY id If we query the data in the table we see the following output. INSERT INTO null_test_tab values (4, NULL, NULL, 'THREE', 'THREE')

INSERT INTO null_test_tab values (3, NULL, NULL, 'THREE', 'FOUR') INSERT INTO null_test_tab values (2, NULL, 'TWO', 'THREE', 'FOUR') INSERT INTO null_test_tab values (1, 'ONE', 'TWO', 'THREE', 'FOUR') Most of the examples in this article require the following table. For a more detailed description follow the links are the bottom of the article. This article provides a summary of the functions available for handling null values. Home » Articles » Misc » Here NULL-Related Functions

Pl sql decode example