1
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

MT0
  • 143,790
  • 11
  • 59
  • 117
dhruv kadia
  • 45
  • 1
  • 3
  • 10
  • Do you mean "replace null with empty strings?" – JosephStyons May 06 '16 at 18:18
  • Replace empty strings (which are the same as null in Oracle), or values which are just whitespace? – Alex Poole May 06 '16 at 18:19
  • @AlexPoole empty strings are not the same as null. – JosephStyons May 06 '16 at 18:19
  • @JosephStyons - they are the same in Oracle (varchar and varchar2 etc.). [Currently, anyway](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements005.htm#SQLRF30037) *8-) – Alex Poole May 06 '16 at 18:21
  • @JosephStyons In Oracle, yes they are. – MT0 May 06 '16 at 18:21
  • @AlexPoole but, but... oh. ok. you're right. Is that recent? I haven't worked with Oracle since 11g but I could've sworn they were different (as they are in SQL Server). – JosephStyons May 06 '16 at 18:27
  • @JosephStyons The `VARCHAR` dataype has always been a synonym for the `VARCHAR2` datatype - in a later version they may implement the `VARCHAR` datatype (and then an empty string will not be the same as a `NULL` when you are using that datatype) but it is not implemented yet and there is no indication that it will be implemented soon (but you never know). But while we are using `VARCHAR2` then an empty string is the same as a `NULL` value. – MT0 May 06 '16 at 18:29

2 Answers2

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
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 = ' '
Dave Costa
  • 47,262
  • 8
  • 56
  • 72