This week we will look at how to use functions in our SELECT statements. Functions are used for a number of
purposes: to change the format of our data for display, to perform calculations, or to manipulate character data.
Functions allow us to provide data from the database in response to more complex business problems. They also will
allow us to produce summarized data in the form of maximum, minimum, and average values.
String functions allow us to manipulate string fields and present the data in different ways. For example, the
Consumers table in the Coffee Merchant database has a field for LastName and a field for FirstName. Last week we
combined those fields into one column and changed the way we output the data by displaying just the first letter of
the first name, using the Left() function:
SELECT LastName + ', ' + Left(FirstName, 1) as "Full Name"
ORDER BY LastName;
A space is a valid character. If you want a space in the output, you must include it inside single quotes: ', '
Also, remember that alias columns, such as the "Full Name" column alias in SELECT LastName + ', ' +
Left(FirstName, 1) as "Full Name" MUST be in quotation marks if the column alias contains a space. If the
column alias doesn't contain a space, then the column alias doesn't need quotes. For example,
Select LastName + ', ' + FirstName as Student doesn't require the world Student in quotes, because there are
not spaces in the word Student.
The Left() function extracts characters from the left-hand side of a string, while Right() extracts characters from the
right-hand side of a string. To extract characters from the middle of a string, use Substring(). This function has 3
arguments: the string you are extracting characters from, the position of the first character you want to extract, and
the number of characters to extract. The Phone number in the Consumers table includes the area code and is in the
format: (501) 555-6079. If we would like to see a list of area codes where our consumers are located, we need to
extract 3 characters beginning at character position 2:
SELECT DISTINCT Substring(Phone, 2, 3) as "Area Code"
ORDER BY 1;
Remember, the DISTINCT keyword will suppress duplicate area codes from being returned – we only want to see
each area code once in the list. Without DISTINCT we would return the area code for every consumer even if many
consumers live in the same area code.
There are a number of very useful functions that allow us to extract various parts of a date and to do mathematical
calculations on dates. Let’s review some of them.