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.

Improve this question
4
  • 1
    well did you run any tests to see for yourself? Commented Apr 17, 2023 at 20:32
  • I tried running below query select * from PRODUCTS where trim(NAME) ='' or NAME=''; select * from PRODUCTS NAME is null; Commented Apr 17, 2023 at 20:35
  • Oracle Database treats a character value with a length of zero as null. 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...) Commented Apr 17, 2023 at 20:36
  • 1
    This could perhaps be considered a duplicate of this question. Commented Apr 17, 2023 at 20:42

1 Answer 1

Reset to default
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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.