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

Question

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

Replace empty strings (which are the same as null in Oracle), or values which are just whitespace? — Alex Poole
– Alex Poole, Commented May 6, 2016 at 18:19
@JosephStyons - they are the same in Oracle (varchar and varchar2 etc.). Currently, anyway *8-) — Alex Poole
– Alex Poole, Commented May 6, 2016 at 18:21

MT0 · Accepted Answer · 2016-05-06 18:19:22Z

1

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.

answered May 6, 2016 at 18:19

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Comments

Dave Costa · Accepted Answer · 2016-05-06 18:22:46Z

1

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 = ' '

answered May 6, 2016 at 18:22

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related