## SQLWays - Oracle Functions and Expressions

This chapter describes functions and expressions that can be used in Oracle SQL statements and PL/SQL programs.

- Oracle Expressions
  - DECODE Expression
- Oracle Functions
  - LENGTH
  - NVL
  - SUBSTR
  - SYSDATE

### Oracle 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.

- DECODE Expression

#### DECODE Expression

Syntax

     DECODE(expr, search, result {, search, result} [, default])

Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.

The search, result, and default values can be derived from expressions.

Example

This query decodes the value DEPTNO. If DEPTNO is 10, the query returns 'ACCOUNTING'; if DEPTNO is 20, it returns 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the query returns 'NONE'.

    SELECT DECODE (deptno,10, 'ACCOUNTING',
                          20, 'RESEARCH',
                          30, 'SALES',
                          40, 'OPERATION',
                              'NONE') name, loc
    FROM dept;

Equivalents in other databases

IBM DB2 | Simple CASE expression
Microsoft SQL Server | Simple CASE expression

### Oracle 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.

- LENGTH
- NVL
- SUBSTR
- SYSDATE

#### LENGTH

Syntax

     LENGTH(string_expr)

LENGTH returns the number of characters of the giving string expression.

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

IBM DB2 | LENGTH function
Microsoft SQL Server | LEN function

#### NVL

Syntax

     NVL(expr1, expr2)

If expr1 is NULL, the function returns expr2. If expr1 is not NULL, the function returns expr1. The NVL function is usually used to replace NULL values with specified 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, NVL(price, 0.00) AS price
    FROM titles;

Equivalents in other databases

IBM DB2 | COALESCE function
Microsoft SQL Server | ISNULL and COALESCE functions

#### SUBSTR

Syntax

     SUBSTR(string, start [, length])

SUBSTR returns part of a character string beginning at start position, length characters long.

- if start is positive, Oracle counts from the beginning of the string.
- if start is negative, Oracle counts backwards from the end of the string.
- if length is omitted, Oracle returns all characters to the end of the string.

Example

This example returns the full first name in one column, and only the first initial in the second column.

    SELECT au_fname, SUBSTR(au_fname, 1, 1)
    FROM AUTHORS;

    Here is the result set:

    au_fname
    -------------------- ----
    Albert               A
    Ann                  A
    Anne                 A
    Michael              M
    Michel               M

Equivalents in other databases

IBM DB2 | SUBSTR, LEFT and RIGHT functions
Microsoft SQL Server | SUBSTRING , LEFT and RIGHT functions

#### SYSDATE

Syntax

     SYSDATE

The SYSDATE function returns the current date and time.

Example

This example selects the current system date and time.

    SELECT SYSDATE
    FROM dual;

Equivalents in other databases

IBM DB2 | CURRENT TIMESTAMP special register
Microsoft SQL Server | GETDATE function

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