2

When I insert an empty String in a database table column, it looks like it is internally stored as NULL. So why can I not select the respective row by looking for exactly that empty string?

Example:

insert into mytable (mycolumn,col2,col3) values ('','xyz','abc');

// now there is a row having mycolumn == NULL

select * from mytable where mycolumn='';

// empty :(

What can I do about that?

Lokomotywa
  • 2,624
  • 8
  • 44
  • 73
  • Related http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null/203536#203536 – Justin Cave Jul 12 '16 at 17:51

4 Answers4

7

This is a weird anachronism in Oracle (using default settings). Oracle does, indeed, treat an empty string as NULL. This includes in comparisons, so:

where mycolumn = ''

is the same as:

where mycolumn = NULL

And this never returns true (NULL <> NULL).

My advice? Get used to using NULL explicitly and writing:

where mycolumn is null
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Interested in your side note about "using default settings." I wasn't aware there is a setting where you can ask Oracle to treat empty strings as different from NULL. Can you please elaborate? Thanks! –  Jul 12 '16 at 16:07
  • @gordon-linoff What happens when we insert `''` to a CHAR(1) field? – Srini V Jul 12 '16 at 16:11
  • @realspirituals . . . `NULL` is inserted. – Gordon Linoff Jul 13 '16 at 00:58
  • @Ferenjito . . . Oh, it's also really fun when you use functions such as `translate()`. But you'll quickly get used to it. – Gordon Linoff Jul 13 '16 at 00:59
  • ... how to check if the empty string is contained in a query? Like `select from table_A where in (select value from table_B)`? Note that the `X` is a paramter so I don't know whether it is the empty string or something else so I wouldn't be able to use `EXISTS (select value from table_B where value is null)` because I do not know if I have to use `is null` or `= X`... IMHO if Oracle wanted to treat `''` the same way as `NULL` they should also have made sure that `= ''` is the same as `is null`... Unfortunately I'm stuck on Oracle 9.2 and cannot change settings – GACy20 Mar 11 '22 at 09:09
2

Because Oracle is weird that way. It treats NULL and empty string as identical. And, because of that, anything with = '' or != '' will have a logical value of UNKNOWN, never TRUE. To check for equality or inequality with the empty string you must check for mycolumn IS NULL or IS NOT NULL. (This part though, about checking for NULL, is not Oracle - it is SQL standard.) In any case, in Oracle the equality condition is = rather than ==.

1

Read here

An empty string is treated as a null value in Oracle. It is also important to note that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions.

Srini V
  • 11,045
  • 14
  • 66
  • 89
0

NULL values represent missing or unknown data. NULL values are used as placeholders or as the default entry in columns to indicate that no actual data is present. The NULL is untyped in SQL, meaning that it is not an integer, a character, or any other specific data type.

Note that NULL is not the same as an empty data string or the numerical value '0'. While NULL indicates the absence of a value, the empty string and numerical zero both represent actual values.

While a NULL value can be assigned, it can not be equated with anything, including itself.It is also important to note that the null value is unique

Because NULL does not represent or equate to a data type, you cannot test for NULL values with any comparison operators, such as =, <, or <>.

This may help you to understand clearly about NULL in Oracle.

Goutham
  • 52
  • 1
  • 1
  • 9