CODEWITHSUNDEEP
oracle-databasenvl
dhruv kadia
dhruv kadia

Reputation: 55

How to replace empty string with null value in oracle using nvl function

INSERT INTO books(book_id, title, author_last_name, author_first_name, classify)
VALUES (1000, 'The Picture of Dorian Gray', 'Wilde', 'Oscar', 'fiction');
INSERT INTO books(book_id, title, author_last_name, author_first_name, classify)
VALUES (1001, 'Think Big', 'Carson', 'Dr. Ben', 'non - fiction');
INSERT INTO books(book_id, title, author_last_name, author_first_name, classify)
VALUES (1003, 'Mathematical Scandals', 'Pappas', ' ', 'non - fiction');
INSERT INTO books(book_id, title, author_last_name, author_first_name, classify)
VALUES (1004, 'SQL', 'Harris', 'Andy', 'non - fiction');
INSERT INTO books(book_id, title, author_last_name, author_first_name, classify)
VALUES (1010, 'Excel 2016', 'Chan', ' ', 'non - fiction');
commit;

here is the value of my table. in some records i have inserted empty string ''. how to replace that empty string with NULL values using nvl function

Upvotes: 1

Views: 5032

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

The NVL function is used to replace NULL with some other value, not the reverse.

If you had actually entered an empty string, your question would be irrelevant. In Oracle, there is no distinction between NULL and the empty string ''.

But it appears you've entered strings containing a single space, which is not the same as an empty string.

A simple update to replace these strings with NULL would be:

UPDATE books SET
  author_first_name = NULL
WHERE
  author_first_name = ' '

Upvotes: 1

MT0
MT0

Reputation: 167962

This will replace the single whitespace strings with a NULL value:

UPDATE books
SET    author_first_name = NULL
WHERE  author_first_name = ' ';

Or, this will trim all the whitespace from the start and end of the names (and if an empty string is left then that is the same as a NULL value)

UPDATE books
SET    author_first_name = TRIM( author_first_name );

The NVL function is used to conditionally replace NULL values with another value - not the other way round so it is inappropriate to use it for this purpose.

Upvotes: 1

Related Questions