T-SQL 101: Built-In Functions

T-SQL has many built-in functions that come in four basic flavors: aggregate, ranking, rowset, and scalar. You’ve already been introduced to aggregate functions, which take in sets of data and return a single aggregated value. Because I covered several aggregate functions in “T-SQL 101, Lesson 3,” May 2008, I won’t cover them here.

Ranking functions are used to determine the particular rank of a record in a portion of a result set. Rowset functions generate a result set that can be referenced in T-SQL statements as if it were a table. Due to the complex nature of the ranking and rowset functions, they’re beyond the scope of this lesson. If you’d like to find out more about them, see the SQL Server 2005 Books Online (BOL) documentation at msdn.microsoft.com/en-us/library/ms189798.aspx for ranking functions and msdn.microsoft.com/en-us/library/ms187957.aspx for rowset functions.

Scalar functions can accept one or more input parameters but return only a single value as output. You can use scalar functions in any valid expression, including another scalar function, so you can use them to perform a wide variety of operations. You can use scalar functions for converting dates, truncating strings, and even performing advanced mathematical operations that involve trigonometry. In this lesson, I’ll introduce you to several common scalar functions used for working with dates and manipulating strings. I’ll leave the trigonometric functions for a much later lesson.

The Prerequisites

To follow along with the examples in this lesson, make sure your MyDB database contains the following:

If you haven’t created this table and view, you’ll find the code to do so in the 99832.zip file. To download this file, click the "Download the Code" hotlink at the top of the article.

Functions for Working with Dates

DBAs commonly work with dates, so let’s start by examining three date-related functions: GETDATE, DATENAME, and DATEADD.

GETDATE. In T-SQL, you can use the GETDATE function to obtain the current system date and time. Although GETDATE doesn’t have any input parameters, you still need to include the parentheses in your code because that’s how SQL Server typically identifies functions. To use this function, you include it in a query statement such as

SELECT GETDATE() AS 'Current Date/Time'

Because you’re accessing the GETDATE function from within a SELECT statement, the result is treated as if it were a column being retrieved from a table, like this

Current Date/Time2008-05-31 22:20:52.917

DATENAME. Now let’s say you want to determine on what day of the week a particular date will occur. You could write some complex calendar code, or you could use the DATENAME function. As the following syntax shows, DATENAME requires two input parameters:

DATENAME(datepart, date)

The datepart parameter specifies the part of the date to retrieve, and the date parameter specifies the date. For example, to determine on what day of the week New Year’s Eve occurs this year, you’d execute the code

SELECT DATENAME(dw,'2008-12-31')  AS 'New Year''s Eve'

which would give you the result

New Year's EveWednesday

This example uses dw as the first input parameter, which tells SQL Server that the part of the date you want to retrieve is the day of the week. There are 11 datepart parameters you can specify, including mm or m for month and dy or i for day of the year (e.g., day 360 of the year). You can read more about the other datepart parameters in the SQL Server 2005 BOL documentation at msdn.microsoft.com/en-us/library/ms174395.aspx.

SQL Server automatically converts strings to datetime values if the data is in a recognized format. In this example, the date passed to the DATENAME function was specified as a string in the format YYYY-MM-DD, which SQL Server recognizes. Because a time wasn’t specified, SQL Server assumes the time is midnight.

In the SELECT statement’s AS clause, note the two apostrophes (i.e., single quotes) in the column name. Because single quotes are used to denote the start and stop of character strings, you have to specify two single quotes if you want the string to contain an apostrophe.

DATEADD. If you need to add or subtract a specified period of time from a date, you can use the DATEADD function. This function has three input parameters:

DATEADD(datepart, number, date)

You use the first parameter to specify the part of the date you’re interested in adding time to or subtracting time from. Valid datepart parameters include hh for hour, dd or d for day, and yyyy or yy for year. (You can find a comprehensive list of datepart parameters for the DATEADD function at msdn.microsoft.com/en-us/library/ms186819.aspx.) The second parameter is an integer that specifies how many of the time periods specified by datepart to add or subtract. You need to use a negative value to indicate subtraction. The third parameter is a valid string that specifies the date you want to add time to or subtract time from.

For example, to find the date seven days after New Year’s Eve, you’d use the code

SELECT DATEADD(d,7,'2008-12-31')  AS '7 Days Later'

The results would look like

7 Days Later2009-01-07 00:00:00.000

To determine the current date and time as well as the date and time 12 hours ago, you can use the GETDATE and DATEADD functions in the statement

SELECT GETDATE() AS 'Now',  DATEADD(hh,-12,GETDATE())  AS '12 Hours Ago'

As this statement demonstrates, you can embed GETDATE within another scalar function. The order of precedence dictates that innermost functions are executed first. Figure 1 shows sample results from this statement.

Functions for Manipulating Strings

T-SQL has more than 20 string-manipulation functions that let you perform all sorts of useful operations ranging from simple string truncation to the more complex search and replace. Commonly used string-manipulation functions include LEFT, RIGHT, SUBSTRING, and REPLACE.

LEFT and RIGHT. The LEFT and RIGHT functions return a specified number of characters from the left or right side of an input string. These functions’ syntaxes are

LEFT(character_exp, integer_exp)RIGHT(character_exp, integer_exp)

where character_exp is the character expression and integer_exp is an integer that specifies the number of characters to return from either the start of the character expression (LEFT function) or the end of the character expression (RIGHT function). A character expression can be a hard-coded string, a column of characters from a query, or binary data from a query.

To see these two functions in action and compare their differences, execute the code

SELECT LEFT('ABCDEFGH',4) AS 'First 4',  RIGHT('ABCDEFGH',4) AS 'Last 4'

The results are

First 4 Last 4ABCD EFGH

SUBSTRING. The SUBSTRING function returns a portion of a string. It has three input parameters:

SUBSTRING(expression, start, length)

The first parameter is the expression (character string, binary string, text, image, or column) from which the substring will be retrieved. The second parameter is an integer that specifies the start position of the desired substring within the expression. The third parameter is an integer that indicates how many characters to return.

For example, the following code returns several substrings from hard-coded strings:

SELECT 'ABCDEFGH' AS 'String',  SUBSTRING('ABCDEFGH',1,5)  AS 'First Five',   SUBSTRING('ABCDEFGH',2,6)  AS 'Middle Six',  SUBSTRING('ABCDEFGH',6,3)  AS 'Last Three'

As you can see in the results in Figure 2, by specifying different starting positions and lengths, you have full control over the substring returned by the function.

Now let’s see how the SUBSTRING function can be used to extract substrings from columns in a table. Run the following query against the Employee table:

USE MyDBSELECT FirstName, LastName,SUBSTRING(FirstName,1,4) +SUBSTRING(LastName,4,4) AS 'NickName'FROM Employee

This query uses two SUBSTRING commands. The first command is run against the FirstName column and generates a substring of the first 4 characters of each employee’s first name. The second command is run against the LastName column and attempts to generate a substring four characters long, starting at position four. I said “attempts to generate” because some of the last names don’t have four characters starting at position four. For example, the last name of Smith has only two characters (th) starting at that position. In such cases, the function simply returns what’s there and doesn’t generate an error. The results of the two substring commands are concatenated (i.e., added together), providing a list of silly nicknames for the employees, as Figure 3 shows.

REPLACE. The REPLACE function finds every occurrence of a substring inside a string, then replaces the old substrings with a new substring. This function has three parameters:

REPLACE(original_string,  string_to_find, replacement_string)

The original_string parameter is the string to be searched. The string_to_find parameter is the substring you want to find and replace. The replacement_string parameter is the replacement substring. For example, the code

SELECT 'I talked the talk.'  AS 'Before',  REPLACE('I talked the talk.',  'alk','ank') AS 'After'

searches the string I talked the talk, replacing every occurrence of the substring alk with ank. Here are the results:

Before AfterI talked the talk. I tanked the tank.

The length of the substring being replaced doesn’t have to match the length of the replacement substring, as this example shows

SELECT Movie, Stars,  REPLACE(Stars,'*','STAR')  AS 'Replaced'  FROM vMovieReviewSummaryORDER BY Stars

Figure 4 shows the results.

A Little Research Can Save You a Lot of Time

When it comes to built-in T-SQL functions, I’ve barely scratched the surface. I hope that what I covered in this lesson will give you a basic understanding of how to use scalar functions in your own queries and where to find out about the other types of built-in functions. Don’t be like one of my colleagues who was shocked when I removed about 500 lines of buggy code from his stored procedure and replaced it with a single REPLACE command. It amazes me how much effort people pour into reinventing the wheel because they don’t bother to do a little research upfront. Me? I’m too lazy to be inefficient!