Timeline for Oracle empty string converts to null
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 12, 2013 at 19:54 | comment | added | Philᵀᴹ | @kainaw Sorry, you don't understand! | |
| Sep 12, 2013 at 18:39 | comment | added | kainaw | @MindaugasRiauba In the example above, I demonstrated that Oracle converts '' to NULL on insert commands. It does not convert '' to NULL on select commands. So, the statement "Oracle converts '' to NULL" is not entirely correct. | |
| Sep 12, 2013 at 18:38 | comment | added | kainaw | @LeighRiffel Your answer is based on an incorrect reading of the results. When using '', you get no results. When using NULL, you get a result with NULL in it. There is a huge difference between getting no result and getting a record with a NULL in it. Imagine if there were two columns. Using '', you get no records. Using NULL, you get the value from the first column and a NULL value for the second - which is not "no record". | |
| Sep 12, 2013 at 18:30 | comment | added | Mindaugas Riauba |
Oracle converts zero length varchars to NULLs. If you will create your table with f not null constraint your insert will fail with "cannot insert NULL" error.
|
|
| Sep 12, 2013 at 17:51 | comment | added | Leigh Riffel |
You get no results using WHERE f= '' for the same reason you get no results using WHERE f=NULL. In Oracle '' and NULL are the same so they both evaluate to false.
|
|
| Sep 12, 2013 at 16:15 | comment | added | kainaw | That cannot be true. It must be doing a conversion. If I insert a '', and there is no conversion, I will be able to select a ''. But, I cannot. I must select a NULL to get the '' I inserted. | |
| Sep 12, 2013 at 15:15 | history | answered | Nicolas Durand | CC BY-SA 3.0 |