Home Forums Main Forums SAS Forum NVL and COALESCE() functions in SQL

  • NVL and COALESCE() functions in SQL

     Justin updated 7 months, 2 weeks ago 1 Member · 2 Posts
  • Justin

    Administrator
    February 6, 2021 at 6:13 pm

    The COALESCE function in SQL returns the first non-NULL expression among its arguments. It is the same as the following CASE WHEN statement:

     SELECT 
    CASE ("column_name") WHEN "expression 1 is not NULL" THEN "expression 1"
    WHEN "expression 2 is not NULL" THEN "expression 2"
    ...
    [ELSE "NULL"]
    END
    FROM "table_name"

    For examples, say we have the following table Table Contact_Info
    Name Business_Phone Cell_Phone Home_Phone
    Jeff 531-2531 622-7813 565-9901
    Laura NULL 772-5588 312-4088
    Peter NULL NULL 594-7477

    and we want to find out the best way to contact each person according to the following rules:
    1. If a person has a business phone, use the business phone number.
    2. If a person does not have a business phone and has a cell phone, use the cell phone number.
    3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

    We can use the COALESCE function to achieve our goal:

    SELECT Name, 
    COALESCE(Business_Phone, Cell_Phone, Home_Phone) as Contact_Phone
    FROM Contact_Info;

    Result:
    Name Contact_Phone
    Jeff 531-2531
    Laura 772-5588
    Peter 594-7477

    They do the same job as the below SAS code:

    If Business_phone is not null then contact_phone=business_phone;
    Else if cell_phone is not null then contact_phone=cell_phone;
    Else if home_phone is not null then contact_phone=home_phone;
    Else contact_phone=”N/A”;

    Isn’t it cool? Then practice and use it!

  • Justin

    Administrator
    February 6, 2021 at 6:09 pm

    The NVL() function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

    For example, if we have the following table:
    Table Sales_Data
    store_name Sales
    Store A 300
    Store B NULL
    Store C 150

    The following SQL code:

     SELECT SUM (NVL(Sales,  100))  FROM Sales_Data; 

    returns 550.

    This is because NULL has been replaced by 100 via the ISNULL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

    Please note: NVL function works with both character and numeric variables.

    NVL(sales, 0);
    *** Equivalent to this in SAS: If missing(sales) then sales= 0;

    NVL(Name, “Not available”);
    *** Equivalent to this in SAS: If missing(names) then names=”Not available”;

Log in to reply.

Original Post
0 of 0 posts June 2018
Now