0

PRODUCTS Table having column id and name.

insert into PRODUCTS(id,name)
values(1,'');

insert into PRODUCTS(id,name)
values(2,null);

insert into PRODUCTS(id,name)
values(1,'product1');

==========================
ID    Name
1     (null)
2     (null)
3     product1

so my question is:

  1. Can we fetch the row having name as ''?
  2. Can we fetch the row having name as null?
  3. Is '' and null in oracle same?
  4. Can we differentiate between these 2 ('' and null) in oracle package?

Thanks in advance.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Piyush
  • 5,145
  • 16
  • 49
  • 71
  • 1
    well did you run any tests to see for yourself? – OldProgrammer Apr 17 '23 at 20:32
  • I tried running below query select * from PRODUCTS where trim(NAME) ='' or NAME=''; select * from PRODUCTS NAME is null; – Piyush Apr 17 '23 at 20:35
  • [Oracle Database treats a character value with a length of zero as null.](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html) So - sort of (with `is null`); yes (also with `is null`); yes; and no. You can't use `=`. (Also be aware of padded and non-padded character semantics...) – Alex Poole Apr 17 '23 at 20:36
  • 1
    This could perhaps be considered a duplicate of [this question](https://stackoverflow.com/q/203493/266304). – Alex Poole Apr 17 '23 at 20:42

1 Answers1

1

To your questions:

  1. Can we fetch the row having name as ''?

Yes: select * from products where name is null;

  1. Can we fetch the row having name as null?

Yes: select * from products where name is null;

  1. Is '' and null in oracle same?

Yes, Oracle does not make any difference between '' (empty string) and null.

  1. Can we differentiate between these 2 ('' and null) in oracle package?

No. (afaik)

The Impaler
  • 45,731
  • 9
  • 39
  • 76