Database Tutorial

Course Tutorial Site

Site Admin

SQL Functions

with 4 comments

This web page describes what a SQL function are, and shows you how to use them. SQL functions are pass-by-value functions. That means you put copies of material into the function as call parameters, the function consumes the original values, and returns a processed output value. In the purest programming sense SQL functions are factories that produce what are known as expressions.

SQL functions come in two varieties, those delivered as part of the database management system engine and those created through procedural languages supported by the same engine. These functions perform many utilities inside SQL statements, like changing and/or formatting one data type into another, performing complex mathematical resolutions (dates), or supplying flow control to a non-procedural language.

This page shows you that process by showing you how to handle null values. Null values are special in SQL. They use what is known as three-value logic, which differs from two-value logic where what isn’t true is false and vice versa. It also shows you how to cast data types from one type to another with SQL functions.

There isn’t a convenient way to substitute a value for a null value in a result set, except by calling the implementation dependent function.

Written by michaelmclaughlin

October 23rd, 2009 at 8:46 am

Posted in

4 Responses to 'SQL Functions'

Subscribe to comments with RSS or TrackBack to 'SQL Functions'.

  1. The beginning of the page makes more sense if one reads the second paragraph first. Once I read that, I could understand what was going on in the first paragraph. The first paragraph talks about functions, but the concept of functions is not delineated until the second paragraph. The description of a concept’s use is useless until after the concept has been defined. That is how I read things, at least. Not sure I’m really qualified to comment…

    Sam Johansen

    4 Feb 10 at 1:22 pm

  2. I concur. It’s switched now.

    michaelmclaughlin

    4 Feb 10 at 8:42 pm

  3. “There isn’t a convenient way to substitute a value for a null value in a result set, except by calling the implementation dependent function.”

    Is COALESCE not an implementation independent function and is there a reason we would not want to use it over the functions you mentioned (NVL, ISNULL, etc.)?

    Ryan D

    17 May 10 at 2:21 pm

  4. The most portable is:

    CASE
      WHEN some_value IS NULL
      THEN another VALUE
      ELSE some_value
    END

    The COALESCE function is used to find the first null value in a list of values.

    michaelmclaughlin

    17 May 10 at 2:30 pm

Leave a Reply