![]() WHERE DECODE(col1, NULL, '!null!', col1) = DECODE(col2, NULL, '!null!', col2) It is possible to make this happen using the NVL and DECODE functions, but depending on how you use them this relies on you converting the null value to another value that you hope will never be present in the column or variable. We have seen that a comparison of "NULL = NULL" will always return false, but sometimes you want it to return true. SELECT id, col1, NANVL(col1, 0) AS output FROM nanvl_test_tab Next, we query the data again, but convert any "NaN" values to "0" using the NANVL function. SELECT * FROM nanvl_test_tab ORDER BY id If we query the table we see the following data. INSERT INTO nanvl_test_tab VALUES (4, 'NaN') INSERT INTO nanvl_test_tab VALUES (3, '-INF') INSERT INTO nanvl_test_tab VALUES (2, 'INF') INSERT INTO nanvl_test_tab VALUES (1, 1234.5678) The following table will be used to demonstrate it. The function is similar to NVL, but rather than testing for null it tests for "NaN" values. The NANVL function was introduced in Oracle 10g for use with the BINARY_FLOAT and BINARY_DOUBLE datatypes, which can contain a special "Not a Number" or "NaN" value. SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id If the condition evaluates to true, it returns false. If this condition evaluates to false or unknown, it returns true. It is used in a where clause to evaluate a condition. The LNNVL function has been available since at least Oracle 9i, but was undocumented until Oracle 11g. SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id In our test table the values of CO元 and COL4 are equal in row 4, so we would only expect null returned for that row using the following query. If they are not equal, the first parameter value is returned. It accepts two parameters and returns null if both parameters are equal. The NULLIF function was introduced in Oracle 9i. SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id If all parameters contain null values, it returns null. It accepts two or more parameters and returns the first non-null value in a list. The COALESCE function was introduced in Oracle 9i. All other rows contains null in COL1, so the value of CO元 is returned. The first row in the test table has a not null value in COL1, so the value of COL2 is returned. SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id The following query shows NVL2 in action. If the first parameter value is null, it returns the third parameter. 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 ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |