Syntax: DECODE : DECODE « Conversion Functions « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Conversion Functions
  3. DECODE

Syntax: DECODE

   
SQL>
SQL> CREATE TABLE myCar
  2  (
  3    MAKER  VARCHAR (25),
  4    MODEL  VARCHAR (25),
  5    PRICE  NUMERIC
  6  );

Table created.

SQL>
SQL> INSERT INTO myCar VALUES('CHRYSLER','CROSSFIRE',33620);

1 row created.

SQL> INSERT INTO myCar VALUES('CHRYSLER','300M',29185);

1 row created.

SQL> INSERT INTO myCar VALUES('HONDA','CIVIC',15610);

1 row created.

SQL> INSERT INTO myCar VALUES('HONDA','ACCORD',19300);

1 row created.

SQL>
SQL> INSERT INTO myCar VALUES('FORD','MUSTANG',15610);

1 row created.

SQL> INSERT INTO myCar VALUES('FORD','LATESTnGREATEST',NULL);

1 row created.

SQL> INSERT INTO myCar VALUES('FORD','FOCUS',13005);

1 row created.

SQL>
SQL> SELECT * FROM  myCar;

MAKER                     MODEL                          PRICE
------------------------- ------------------------- ----------
CHRYSLER                  CROSSFIRE                      33620
CHRYSLER                  300M                           29185
HONDA                     CIVIC                          15610
HONDA                     ACCORD                         19300
FORD                      MUSTANG                        15610
FORD                      LATESTnGREATEST
FORD                      FOCUS                          13005

7 rows selected.

SQL>
SQL> SELECT
  2     model,
  3     DECODE(maker, 'CHRYSLER','DOMESTIC'
  4           ,'FORD','DOMESTIC'
  5           ,'FOREIGN')
  6  FROM myCar;

MODEL                     DECODE(M
------------------------- --------
CROSSFIRE                 DOMESTIC
300M                      DOMESTIC
CIVIC                     FOREIGN
ACCORD                    FOREIGN
MUSTANG                   DOMESTIC
LATESTnGREATEST           DOMESTIC
FOCUS                     DOMESTIC

7 rows selected.

SQL>
SQL>
SQL> drop table myCar;

Table dropped.








Related examples in the same category

1.Decode char value to number
2.Decode statement could be easily rewritten using the CASE statement
3.Decode for more than one key value pair
4.Use Decode as if statement to logic like 'if then else'
5.DECODE and GROUPING
6.Create a report that shows number of customers per state.
7.Use DECODE and SIGN functions on the ORD table
8.Extract any procedure, function or package
9.Decode the result from row_number over, partition by, order by
10.Decode a column with substr and instr
11.DECODE and substr function
12.Format nullable heading
13.DECODE in the GROUP BY clause
14.Demo range comparison with DECODE
15.Use decode as if statement and output 'high' or 'low'
16.Use decode create dynamic select statement
17.decode column data to output more meaningful information