RelationalDBDesign 




Advanced SQL   «Prev  Next»
Lesson 1

Introduction to Functions in SQL

When you are working with database engines with capabilities beyond just retrieving your data, it makes sense to take advantage of these features. Many of these additonal features are derived from many of the elements that have already been covered, such as clauses in the SELECT statement, joins, and views.
Another extremely useful capability is provided by an engine's ability to support functions. Functions are predefined actions that can be used on the values that you send the engine.
For example, at a very simple level, you can query the database with the following, having the database complete some addition for you:
SELECT 5 + 5 
The result would be an unnamed column with a value of 10. The engine actually figures out the results for you, returning them as the answer to your "query."
In this module, you will learn how to use several different kinds of functions, providing help for character-based, numeric, and date-formatted values.


The available math functions in SQL are fairly limited, which reflects the use of SQL as a tool for pulling out the raw data to answer questions. Any actual processing of the answers is really something left to high-level programming languages in middleware or front-end applications. For example, a Web server might connect to a database to pull back the name of a customer and credit card details, but you would not expect the database to process the card details. You start by looking at the four basic math functions supported by all flavors of SQL, and then you look at some more sophisticated math functions, which, although not part of the SQL standards, are commonly available on most database systems.

Function Operator
Multiply *
Divide /
Add +
Subtract -

What is not obvious from this list is the order of precedence of the operators.