## SQLWays - Microsoft SQL Server Functions and Expressions

This chapter describes functions and expressions that can be used in Microsoft SQL Server SQL statements and Transact SQL programs.

- SQL Server Expressions
  - Simple CASE Expression
- SQL Server Functions
  - COALESCE
  - GETDATE
  - ISNULL
  - LEFT
  - LEN
  - RIGHT
  - SUBSTRING

### SQL Server 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 expr, and then, in the order specified, evaluates 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 alter the display of book categories to make them more understandable.

    SELECT Category =
          CASE type
             WHEN 'popular_comp' THEN 'Popular Computing'
             WHEN 'mod_cook' THEN 'Modern Cooking'
             WHEN 'business' THEN 'Business'
             WHEN 'psychology' THEN 'Psychology'
             WHEN 'trad_cook' THEN 'Traditional Cooking'
             ELSE 'Not yet categorized'
          END,
       price AS Price
    FROM titles

Equivalents in other databases

IBM DB2 | Simple CASE expression
Oracle | DECODE expression

### SQL Server 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
- GETDATE
- ISNULL
- LEFT
- LEN
- RIGHT
- SUBSTRING

#### COALESCE

Syntax

     COALESCE(expr1 {, exprN })

COALESCE returns the first expression that is not NULL. The expressions 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

IBM DB2 | COALESCE function
Oracle | NVL function

#### GETDATE

Syntax

     GETDATE()

The GETDATE function returns the current system date and time.

Example

This example selects the current system date and time.

    SELECT GETDATE()

    Here is the result set:

    -------------------------
    July 31 1977   7:00    AM

This example creates the Employees table and uses GETDATE for a default value for the employee hire date.

    CREATE TABLE Employees
    (
     id INT NOT NULL,
     name VARCHAR(100) NOT NULL,
     hire_date DATETIME DEFAULT GETDATE()
    )

Equivalents in other databases

IBM DB2 | CURRENT TIMESTAMP special register
Oracle | SYSDATE function

#### ISNULL

Syntax

     ISNULL(check_expr, replacement_value)

ISNULL replaces NULL with the specified replacement value. The function returns the value of check_expr if it is not NULL, otherwise, replacement_value is returned.

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, ISNULL(price, 0.00) AS price
    FROM titles

Equivalents in other databases

IBM DB2 | COALESCE function
Oracle | NVL function

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

IBM DB2 | LEFT function
Oracle | SUBSTR function

#### LEN

Syntax

     LEN(string_expr)

LEN returns the number of characters of the giving string expression, excluding trailing blanks.

Example

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

    SELECT CompanyName, LEN(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
Oracle | LENGTH 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

IBM DB2 | RIGHT function
Oracle | SUBSTR function

#### SUBSTRING

Syntax

     SUBSTRING(expr, start, length)

SUBSTRING returns part of a character, binary or text expression beginning at start position, length characters (bytes) long.

Example

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

    SELECT au_fname, SUBSTRING(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 function
Oracle | SUBSTR function

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