## SQLWays - IBM DB2 Functions and Expressions

This chapter describes functions and expressions that can be used in IBM DB2 SQL statements.

- IBM DB2 Expressions
  - Simple CASE Expression
- IBM DB2 Functions
  - COALESCE
  - LEFT
  - LENGTH
  - RIGHT
- IBM DB2 Special Registers
  - CURRENT TIMESTAMP

### IBM DB2 Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. An expression generally assumes the datatype of its components.

- Simple CASE Expression

#### Simple CASE Expression

Syntax

     CASE expr
       WHEN when_expr THEN result_expr
      {WHEN when_expr THEN result_expr}
      [ELSE else_expr]
     END

Simple CASE evaluates input_expr, and then, in the order specified, evaluates input_expr = when_expr for each WHEN clause. Simple CASE returns the result_expr of the first WHEN clause that evaluates to TRUE. If no match is found, CASE returns the else_expr if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Example

This example uses a CASE expression to list the full name of the division to which each employee belongs.

    SELECT EMPNO, LASTNAME,
            CASE WORKDEPT
             WHEN 'A' THEN 'Administration'
             WHEN 'B' THEN 'Human Resources'
             WHEN 'C' THEN 'Accounting'
             WHEN 'D' THEN 'Design'
             WHEN 'E' THEN 'Operations'
            END
     FROM EMPLOYEE;

Equivalents in other databases

Oracle | DECODE expression
Microsoft SQL Server | Simple CASE expression

### IBM DB2 Functions

A function is a relationship between a set of input data values and a set of result data values. Functions can either be built-in or user-defined.

Built-in functions are provided with the database manager, while user-defined functions are used to extend the function of the database system by users.

- COALESCE
- LEFT
- LENGTH
- RIGHT

#### COALESCE

Syntax

     COALESCE(expr, expr2 {, expr })

COALESCE returns the first argument that is not NULL. The arguments are evaluated in the order in which they are specified. This function is usually used to replace NULL values with a not NULL value.

Example

This example selects title and price for all books. If the price for a given title is NULL, the price shown is 0.00.

    SELECT title, COALESCE(price, 0.00) AS price
    FROM titles;

Equivalents in other databases

Oracle | NVL function
Microsoft SQL Server | COALESCE and ISNULL functions

#### LEFT

Syntax

     LEFT(char_expr, int_expr)

LEFT returns int_expr leftmost characters of char_expr.

Example

This example returns the five leftmost characters of each book title.

    SELECT LEFT(title, 5)
    FROM TITLES;

    Here is the result set:

    -----
    The B
    Cooki
    You C
    The G
    The P

Equivalents in other databases

Oracle | SUBSTR function
Microsoft SQL Server | LEFT function

#### LENGTH

Syntax

     LENGTH(expression)

The LENGTH function returns the length of a value. The argument can be an expression that returns a value of any built-in data type.

If the argument is a string, the function returns the number of characters in the string. The length of fixed-length strings includes blanks. The length of a varying-length string is the actual length, not the maximum length.

Example

This example returns the company name and the number of characters in the name for each company.

    SELECT CompanyName, LENGTH(CompanyName)
    FROM CUSTOMERS

    Here is the result set:

    CompanyName
    ---------------------------- -------
    Consolidated Holdings        21
    Eastern Connection           18
    France restauration          19
    Great Lakes Food Market      23
    Island Trading               14
    Let's Stop N Shop            17

Equivalents in other databases

Oracle | LENGTH function
Microsoft SQL Server | LEN function

#### RIGHT

Syntax

     RIGHT(char_expr, int_expr)

RIGHT returns int_expr rightmost characters of char_expr.

Example

This example returns the five rightmost characters of each author's first name.

    SELECT RIGHT(au_fname, 5)
    FROM AUTHORS

    Here is the result set:

    -----
    lbert
    Ann
    Anne
    chael
    ichel

Equivalents in other databases

Oracle | SUBSTR function
Microsoft SQL Server | RIGHT function

### IBM DB2 Special Registers

A special register is a variable that defined for an application process by the database manager and can be referenced in SQL statements.

- CURRENT TIMESTAMP

#### CURRENT TIMESTAMP

Syntax

     CURRENT TIMESTAMP

The CURRENT TIMESTAMP special register specifies a timestamp that is based on a reading of time-of-day clock when the SQL statement is executed. If this special register is used more than once within a single SQL statement, all values are based on a single clock reading.

Example

This example inserts a row into the CUSTOMERS table. The value of the second column is a timestamp that indicates when the row was inserted.

    INSERT INTO CUSTOMERS VALUES ('ABC Corp', CURRENT TIMESTAMP);

Equivalents in other databases

Oracle | SYSDATE function
Microsoft SQL Server | GETDATE function

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.