Scribd Logo
Upload

Welcome to Scribd!

  • 26 views

    Replace - With Value - If - NOT - Null Replace - With - Value - If - Null

    Uploaded by

    shrinked
    NVL, NVL2, NULLIF, and COALESCE are advanced SQL NULL functions. NVL returns a substitute value if a NULL value is passed in. NVL2 extends NVL to return different values for NULL and non-NULL. NULLIF returns NULL if the expressions are equal, otherwise the first expression. COALESCE returns the first non-NULL expression in a list of expressions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Replace - With Value - If - NOT - Null Replace - With - Value - If - Null

    Uploaded by

    shrinked
    26 views3 pages
    NVL, NVL2, NULLIF, and COALESCE are advanced SQL NULL functions. NVL returns a substitute value if a NULL value is passed in. NVL2 extends NVL to return different values for NULL and non-NULL. NULLIF returns NULL if the expressions are equal, otherwise the first expression. COALESCE returns the first non-NULL expression in a list of expressions.

    Original Description:

    Original Title

    hfhsgdh

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    NVL, NVL2, NULLIF, and COALESCE are advanced SQL NULL functions. NVL returns a substitute value if a NULL value is passed in. NVL2 extends NVL to return different values for NULL and non-NULL. NULLIF returns NULL if the expressions are equal, otherwise the first expression. COALESCE returns the first non-NULL expression in a list of expressions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    26 views3 pages

    Replace - With Value - If - NOT - Null Replace - With - Value - If - Null

    Uploaded by

    shrinked
    NVL, NVL2, NULLIF, and COALESCE are advanced SQL NULL functions. NVL returns a substitute value if a NULL value is passed in. NVL2 extends NVL to return different values for NULL and non-NULL. NULLIF returns NULL if the expressions are equal, otherwise the first expression. COALESCE returns the first non-NULL expression in a list of expressions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    Advanced  SQL NULL  functions: 

    ‐‐NVL 

    ‐‐‐NVL2 

    ‐‐‐‐NullIF 

    ‐‐‐COALESCE 

    NVl : NVL function lets you to substitute a value when a null value is encountered.It is a function that will 
    return  that will return NOT‐ NULL values if a null value is passed to it. 

    Syntax :  NVL(String/Number , replace_with  if null) 

    String/Number :   It is a 1st parameter to test  for the null value 

    replace_with  if null : It is a 2nd parameter which is used to return the value the string/number is  null. 

    Example: 

    Select  NVL(supplier city ,’N/A’) from suppliers; 

    Output: It will return ‘N/A’ for null values of supplier city. 

     SQL statement would retrieve from the table the number of products having  
    LIST_PRICE as NULL using NVL function : 

    SELECT COUNT(NVL(list_price, 0))  
    FROM product_information  
    WHERE list_price IS NULL; 

    NVL2 

    NVL2 is function similar to NVL only the difference is , It extends the functionality of NVL function. It  
    allows  to substitute for null as well as not null values when encountered. 

    Syntax :  NVL(String/Number , replace_with value _if_NOT_ null, replace_with_value _if _null) 

    String/Number is the string/number to test for a null value.

    replace_with value _if_NOT_ null :is the value returned if strin/number is not null.

    replace_with_value _if _null : is the value returned if string/number is null.

    Example: 
    Select  NVL2(supplier city ,’Completed’,’N/A’) from suppliers; 

    If supplier city has null values then o/p is : N/A 

    If supplier city has not null values then o/p is : Completed 

    -------The following example shows whether the income of each employee in department 30 is made up of
    salary plus commission, or just salary, depending on whether the comm column of emp is null or not.

    Select  ename, NVL2(To_Char(COMM), ‘SAL & COMM’ ,SAL’) income 

    From employees where department_no  = 10; 

    NULLIF: 

    NULIIF function is used to return null values if both the values are equal else it will return the 1st value. 

    Note: NULLIF is used in database systems like SQL server  and it is not used  frequently in ORACLE . 

    SYNTAX: 

    NULLIF(expr1,expr2) 

    Note: 

    Expr1 and expr2 must be of either numeric values or of the same datatype 

    The expr1 and expr2 cannot be the literal NULL. 

    Examples: 

    NULLIF(3,3) : o/p – NULL 

    NULLIF(13,12) : o/p = 13 

    NULLIF(‘cat’,’dog’) : o/p == cat 

    NULLIF(NULL,’cat’): o/p‐‐‐Oracle  ERROR 

    COALESCE: 

    COALESCE function is used to take two or more arguments  that are compatible and returns the first 
    argument that is NOT NULL. If all the arguments are null ,then the result is NULL.So it is used to return 
    the first non‐null expression from the list. 
    Syntax : 

    COALESCE(expr1,expr2,expr3,…., expr_n) 

    expr1 to expr_n are the expressions that are needed for testing not‐null values. 

    EXAMPLE: 

    Suppose a Student  table that consists of 3 addresses. Ie address1 , address2 and address3. 

    After Insert statements , the table would be like for Example: 

    Select  from Student; 

    Student_name  | address1 | address2 | address3 

    ABC                  Mumbai      NULL           NULL 

    PQR                  NULL      NULL               Thane 

    XYZ                  NULL      Chembur           NULL 

    STC                  NULL      NULL           NULL 

    Select  COALESCE(address1,address2,address3) from Student 

    1 

    ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ 

    Mumbai 

    Thane 

    Chembur 

    NULL 

    Note : 

    Difference between NVL and COALESCE function: 

    The obvious differences are that coalesce will return the first non null item in it's parameter list whereas
    nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second 

    You might also like