Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/nvl below:

Website Navigation

• Reference
• Function and stored procedure reference
• Conditional expression

NVL | Snowflake Documentation

Categories:

Conditional expression functions

If expr1 is NULL, returns expr2, otherwise returns expr1.

Aliases:

IFNULL

expr1

A general expression.

expr2

A general expression.

• If possible, pass in arguments of the same type. Avoid passing in arguments of different types.

• If one of the arguments is a number, the function coerces non-numeric string arguments (for example, 'a string') and string arguments that aren’t constants to the type NUMBER(18,5).

  For numeric string arguments that aren’t constants, if NUMBER(18,5) isn’t sufficient to represent the numeric value, then cast the argument to a type that can represent the value.

• Either expression can include a SELECT statement containing set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the General usage notes in the Set operators topic.

• The collation specifications of all input arguments must be compatible.

• The collation of the result of the function is the highest-precedence collation of the inputs.

Returns the data type of the returned expression.

If both expressions are NULL, returns NULL.

Create a table that contains contact information for suppliers:

CREATE TABLE IF NOT EXISTS suppliers (
  supplier_id INT PRIMARY KEY,
  supplier_name VARCHAR(30),
  phone_region_1 VARCHAR(15),
  phone_region_2 VARCHAR(15));

Copy

The table contains the phone number for each supplier in two different regions. The phone number can be NULL for a region.

Insert values into the table:

INSERT INTO suppliers(supplier_id, supplier_name, phone_region_1, phone_region_2)
  VALUES(1, 'Company_ABC', NULL, '555-01111'),
        (2, 'Company_DEF', '555-01222', NULL),
        (3, 'Company_HIJ', '555-01333', '555-01444'),
        (4, 'Company_KLM', NULL, NULL);

Copy

The following SELECT statement uses the NVL function to retrieve the phone_region_1 and phone_region_2 values.

This example shows the following results for the NVL function:

• The IF_REGION_1_NULL column contains the value in phone_region_1 or, if that value is NULL, the value in phone_region_2.

• The IF_REGION_2_NULL column contains the value in phone_region_2 or, if that value is NULL, the value in phone_region_1.

• If both phone_region_1 and phone_region_2 are NULL, the function returns NULL.

SELECT supplier_id,
       supplier_name,
       phone_region_1,
       phone_region_2,
       NVL(phone_region_1, phone_region_2) IF_REGION_1_NULL,
       NVL(phone_region_2, phone_region_1) IF_REGION_2_NULL
  FROM suppliers
  ORDER BY supplier_id;

Copy

+-------------+---------------+----------------+----------------+------------------+------------------+
| SUPPLIER_ID | SUPPLIER_NAME | PHONE_REGION_1 | PHONE_REGION_2 | IF_REGION_1_NULL | IF_REGION_2_NULL |
|-------------+---------------+----------------+----------------+------------------+------------------|
|           1 | Company_ABC   | NULL           | 555-01111      | 555-01111        | 555-01111        |
|           2 | Company_DEF   | 555-01222      | NULL           | 555-01222        | 555-01222        |
|           3 | Company_HIJ   | 555-01333      | 555-01444      | 555-01333        | 555-01444        |
|           4 | Company_KLM   | NULL           | NULL           | NULL             | NULL             |
+-------------+---------------+----------------+----------------+------------------+------------------+

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4