COALESCE(state, 'N/A') : coalesce « Conversion Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Conversion Functions
3. coalesce

SQL>
SQL> CREATE TABLE publishers(
  2    pub_id   CHAR(3)     NOT NULL,
  3    pub_name VARCHAR(20) NOT NULL,
  4    city     VARCHAR(15) NOT NULL,
  5    state    CHAR(2)     NULL    ,
  6    country  VARCHAR(15) NOT NULL);

Table created.

SQL>
SQL> INSERT INTO publishers VALUES('P01','A','New York','NY','USA');

1 row created.

SQL> INSERT INTO publishers VALUES('P02','C','San Francisco','CA','USA');

1 row created.

SQL> INSERT INTO publishers VALUES('P03','S','Hamburg',NULL,'Germany');

1 row created.

SQL> INSERT INTO publishers VALUES('P04','T','Berkeley','CA','USA');

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT
  2      pub_id,
  3      city,
  4      COALESCE(state, 'N/A') AS "state",
  5      country
  6    FROM publishers;

PUB CITY            sta COUNTRY
--- --------------- --- ---------------
P01 New York        NY  USA
P02 San Francisco   CA  USA
P03 Hamburg         N/A Germany
P04 Berkeley        CA  USA

SQL>
SQL> drop table publishers;

Table dropped.

SQL>
SQL>