NVL()

Syntax:

NVL(string1, replace_with)

  • The NVL function accepts two arguments:
    The first argument takes the name of the expression to be evaluated.
  • The second argument specifies the value that the function returns when the first argument evaluates to NULL.

 

  • In SQL, NVL is a substitution function that displays one value if another value is NULL. NVL can replace NULL, zero, void, or empty with any value, such as an integer or string.
  • NVL is not an acronym for anything, but thinking of it as a Null Value
  • NVL is an alias for the IFNULL function

Here is an example,
--Create a table and insert a friends name

CREATE TABLE MYFRIENDS(
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
MIDDLENAME VARCHAR(20)
);
-- The data being inserted
INSERT INTO myfriends
VALUES(
'Martin',
'Brooks'
'Cameron'
);

-- The data MISSING VALUES
INSERT INTO myfriends (FIRSTNAME, LASTNAME)
VALUES(
'Martin',
'Brooks'
'Cameron'
);

SELECT FIRSTNAME, MIDDLENAME, LASTNAME, FIRSTNAME ||' ' || NVL(MIDDLENAME, '(no middle name)') || ' '||LASTNAME AS FULLNAME
FROM MYFRIENDS;


Learn more
NVAL2

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top