Lesson 11. Getting to Know the T-SQL Functions
The T-SQL language contains numerous functions you can incorporate into the T-SQL statements you build. These functions perform a variety of important tasks. This lesson covers some of the commonly used numeric, string, date/time, and null-related functions. For additional information on the plethora of T-SQL functions available, consult Books Online (online help for SQL Server) or MSDN. To access Books Online from Management Studio, select View Help from the Help menu. In this lesson, you learn:
How to work with some of the numeric functions available in T-SQL
How to work with some of the string functions available in T-SQL
How to work with some of the date/time functions available in T-SQL
How to use T-SQL to work with nulls
Working with Numeric Functions
Important numeric functions include IsNumeric and ROUND. The sections that follow examine these functions and provide examples of their uses.
Using the IsNumeric Function
The IsNumeric function returns information on whether a value is numeric. Here’s an example:
SELECT City, PostalCode, IsNumeric(PostalCode) FROM Person.Address
The SELECT statement returns each customer’s City, PostalCode, and information on whether the postal code is numeric (see Figure 11.1).
FIGURE 11.1 A SELECT statement that uses the IsNumeric function to determine whether the postal code is numeric.
As you can see in the figure, the postal codes for Ottawa and Burnaby are not numeric, whereas the postal codes for the other cities are numeric.
Exploring the ROUND Function
As its name implies, the ROUND function rounds an expression to a specified length. Here’s an example:
SELECT ProductID, Name, ListPrice, Round(ListPrice, 0) FROM Production.Product
This SQL statement returns the ProductID, Name, ListPrice, and the ListPrice rounded to whole numbers from the Production.Product table (see Figure 11.2).
Notice in the figure that the list price of 133.34 is rounded to 133.00, whereas the list price of 196.92 is rounded to 197.00. This is because all values less than .5 are rounded down, and all values .5 and higher are rounded up.
Taking Advantage of String Functions
Important string functions include LEFT, RIGHT, LEN, REPLACE, REVERSE, REPLICATE, STUFF, SPACE, SUBSTRING, LOWER, UPPER, LTRIM, and RTRIM.
Using the LEFT Function
The LEFT function extracts a designated number of characters from the left of a string:
SELECT ProductDescriptionID,
Description, LEFT(Description, 10)
FROM Production.ProductDescription
This example selects the ProductDescriptionID, the Description, and the ten leftmost characters from the Production.ProductDescription table (see Figure 11.3).
Working with the RIGHT Function
The RIGHT function works similarly but extracts the designated rightmost characters from a string. The same example using the RIGHT function looks like this:
SELECT ProductDescriptionID,
Description, RIGHT(Description, 10)
FROM Production.ProductDescription
This example returns the ProductDescriptionID, Description, and the ten rightmost characters from the Description (see Figure 11.4).
Exploring the LEN Function
The LEN function returns the length of a string. It looks like this:
SELECT ProductDescriptionID,
Description, LEN(Description)
FROM Production.ProductDescription
This example returns the ProductDescriptionID, Description, and the length of the description for each row in the ProductDescription table (see Figure 11.5).
Using the REPLACE Function
The REPLACE function replaces all occurrences of one string with another. Here’s an example:
SELECT ProductDescriptionID,
Description, REPLACE(Description, 'Bike', 'Bicycle')
FROM Production.ProductDescription
This example selects the ProductDescriptionID and Description from the ProductDescription table. It includes an additional column that replaces all occurrences of the word Bike in the Description field with the word Bicycle (see Figure 11.6).
FIGURE 11.6 A SELECT statement that utilizes the REPLACE function to replace all occurrences of the word Bike with the word Bicycle.
Using the REVERSE Function
The REVERSE function returns the reverse image of a string. Here’s an example:
SELECT ProductDescriptionID,
Description, REVERSE(Description)
FROM Production.ProductDescription
This example selects the ProductDescriptionID and Description from the ProductDescription table. It includes an additional column that displays the reverse of the characters in the Description field (see Figure 11.7).
Using the REPLICATE Function
The REPLICATE function replaces all occurrences of one string with another. Here’s an example:
SELECT ProductDescriptionID,
REPLICATE('0', 5 – DATALENGTH(RTRIM(ProductDescriptionID)))
+ RTRIM(ProductDescriptionID) AS PadZero
FROM Production.ProductDescription
This example selects the ProductDescriptionID and the result of an expression that pads the ProductDescription field with leading zeros from the ProductDescription table. It accomplishes this by replicating the correct number of leading zeros based on the length of the trimmed ProductDescriptionID so that all ProductDescriptionID values are five digits in length (see Figure 11.8).
FIGURE 11.8 A SELECT statement that replicates the value 0 so that all ProductDescriptionID values are the same length.
Taking Advantage of the STUFF Function
The STUFF function starts at a specific position and replaces a specified number of characters with other specified characters. Here’s an example:
SELECT ProductDescriptionID,
Description,
STUFF(Description, 5, 10, '**********' ) AS Stuffed
FROM Production.ProductDescription
This example selects the ProductDescriptionID and Description from the Production.ProductDescription table. It includes an additional column that replaces the fifth through fourteenth characters with asterisks (see Figure 11.9).
Using the SPACE Function
The SPACE function returns a specified number of spaces. Here’s an example:
SELECT FirstName + SPACE(1) + LastName
FROM Person.Person
This example returns the FirstName and LastName separated by one space from the Person.Person table (see Figure 11.10).
Using the SUBSTRING Function
The SUBSTRING function extracts specified characters from a string. Here’s an example:
SELECT ProductDescriptionID, Description,
SUBSTRING(Description, 6,10)
AS PartOfDescription
FROM Production.ProductDescription
This example returns the ProductDescriptionID, Description, and the sixth through fifteenth characters of the Description field from the Production.ProductDescription table (see Figure 11.11).
FIGURE 11.11 A SELECT statement that returns the ProductionDescriptionID, Description, and the sixth through fifteenth characters of the Description from the ProductDescription table.
Using the LOWER Function
The LOWER function returns the lowercase version of a string. It looks like this:
SELECT ProductDescriptionID, Description, LOWER(Description)
FROM Customers Production.ProductDescription
The example returns the contents of the ProductDescriptionID and Description fields and then the lowercase version of the contents of the Description field (see Figure 11.12).
FIGURE 11.12 A SELECT statement that returns the lowercase version of the contents of the Description field.
Using the UPPER Function
The UPPER function returns the uppercase version of a string. It looks like this:
SELECT ProductDescriptionID, Description, UPPER(Description)
FROM Customers Production.ProductDescription
The example returns the contents of the ProductDescriptionID and Description fields and then the uppercase version of the contents of the Description field (see Figure 11.13).
FIGURE 11.13 A SELECT statement that returns the uppercase version of the contents of the Description field.
Working with the LTRIM Function
The LTRIM function returns the string without leading spaces. It looks like this:
SELECT ProductDescriptionID, LTRIM(Description)
FROM Production.ProductDescription
The example returns the contents of the ProductionDescriptionID field and then the contents of the Description field with any leading spaces removed.
Working with the RTRIM Function
The RTRIM function returns the string without trailing spaces. It looks like this:
SELECT ProductDescriptionID, RTRIM(Description)
FROM Production.ProductDescription
The example returns the contents of the ProductDescriptionID field and then the contents of the Description field with any trailing spaces removed.
Exploring the Date/Time Functions
Important date/time functions include GETDATE, MONTH, DAY, YEAR, DATEPART, DATENAME, DATEADD, and DATEDIFF. The sections that follow cover these functions.
Using the GETDATE Function
The GETDATE function returns the system date and time. It looks like this:
SELECT GETDATE()
Learning About the MONTH Function
The MONTH function returns the month portion of a date. It looks like this:
SELECT Description, DiscountPct,
StartDate, MONTH(StartDate)
FROM Sales.SpecialOffer
This SQL statement returns the Description, Discount Percent, Start Date, and the month of the Start Date from the SpecialOffer table (see Figure 11.14).
Exploring the DAY Function
The DAY function returns the day portion of a date. It looks like this:
SELECT Description, DiscountPct,
StartDate, DAY(StartDate)
FROM Sales.SpecialOffer
This SQL statement returns the Description, Discount Percent, Start Date, and the month of the Start Date from the SpecialOffer table (see Figure 11.15).
Working with the YEAR Function
The YEAR function returns the year portion of a date. It looks like this:
SELECT Description, DiscountPct,
StartDate, YEAR(StartDate)
FROM Sales.SpecialOffer
This SQL statement returns the Description, Discount Percent, Start Date, and the month of the Start Date from the SpecialOffer table (see Figure 11.16).
Exploring the Powerful DATEPART Function
You use the DATEPART function to extract a part of a date. The first parameter to the DATEPART function is an abbreviation designating the part of the date you want to extract. The second parameter is the date from which you want to extract it. Here’s an example:
SELECT Description, StartDate,
DATEPART(qq, StartDate) AS Quarter,
DATEPART(DAYOFYEAR, StartDate) as DOY
FROM Sales.SpecialOffer
This example selects the Description, the Start Date, the quarter of the Start Date, and the day of the year of the Start Date from the SpecialOffer table (see Figure 11.17).
FIGURE 11.17 A SELECT statement that returns the quarter and day of the year of the StartDate from the SpecialOffer table.
Using the DATENAME Function
The DATENAME function returns a string representing a part of a date. It also receives two parameters. The first is the abbreviation indicating the part of the date you want to extract. The second is the date from which you want to extract it. Here’s an example:
SELECT Description, StartDate,
DATENAME(dw, StartDate) as DOW
FROM Sales.SpecialOffer
This example returns the Description, the Start Date, and a text description of the day of the week of the Start Date (see Figure 11.18).
FIGURE 11.18 A SELECT statement that returns a text description of the day of the week of the StartDate.
Working with the DATEADD Function
You use the DATEADD function to add or subtract time from a date. The first parameter is the time period you want to add or subtract (for example, day, month, year). The second parameter is the number of that time period you want to add or subtract (for example, 1 day, 3 months, or 5 years). The final parameter is the date to which you want to add it or from which you want to subtract it. Here’s an example:
SELECT Description, StartDate,
DATEADD(mm, 1, StartDate) AS Add1Month,
DATEADD(dd, -100, StartDate) AS Subtract100Days
FROM Sales.SpecialOffer
This returns the description, the start date, the date one month after the start date, and the date 100 days before the start date (see Figure 11.19).
Using the DATEDIFF Function
The DATEDIFF function returns the difference between two dates. It receives three parameters. The first is the time period in which you want the difference to appear (days, months, and so on). The second and third parameters are the dates whose difference you want to evaluate. Here’s an example:
SELECT Description, StartDate, EndDate,
DATEDIFF(dd, StartDate, EndDate) AS DayDiff,
DATEDIFF(mm, StartDate, EndDate) AS MonthDiff
FROM Sales.SpecialOffer
This example returns the Description, Start Date, End Date, the number of days between the Start Date and the End Date, and the number of months between the Start Date and the End Date (see Figure 11.20).
FIGURE 11.20 A SELECT statement that returns the number of days and months between the StartDate and EndDate.
Working with Nulls
Several functions help you deal with nulls in your table data. They include ISNULL, NULLIF, and COALESCE. The sections that follow cover these functions.
Exploring the ISNULL Function
The ISNULL function returns information on whether the value in an expression is null. It receives two parameters. The first parameter is the expression you want to evaluate. The second is the value you want to return if the expression is null. The ISNULL function looks like this:
SELECT Description, MinQty,
MaxQty, ISNULL(MaxQty, 100)
FROM Sales.SpecialOffer
This example returns the Description, Minimum Quantity, and Maximum Quantity field values. If the Maximum Quantity is null, the fourth column contains the value 100. Otherwise, the fourth column contains the actual Maximum Quantity value for the record (see Figure 11.21).
Taking Advantage of the NULLIF Function
The NULLIF function replaces specified values with nulls. It receives two parameters. The first is the name of the expression you want to replace. The second is the value you want to replace with nulls. Here’s an example:
SELECT AVG(MinQty) AS WithZero,
AVG(NULLIF(MinQty, 0)) AS NoZero
FROM Sales.SpecialOffer
This example calculates the average Minimum Quantity amount in the SpecialOffer table and then the average Minimum Quantity amount eliminating 0 values from the calculation (see Figure 11.22).
FIGURE 11.22 A SELECT statement that calculates the average minimum quantity amount in the SpecialOffer table, with and without including zero values in the calculation.
Working with the COALESCE Function
The COALESCE function returns the first non-null expression in a series of expressions. SQL Server evaluates the first expression. If it is null, it evaluates the second expression. If the second expression is null, it evaluates the third expression. This continues until the function reaches the last expression. Here’s an example:
SELECT Title, MiddleName, FirstName,
COALESCE(Title, MiddleName, FirstName)
FROM Person.Person
This example returns the Title if it is not null. If the Title is null, it evaluates the contents of the MiddleName field. If it is non-null, the contents of the MiddleName field are returned. Otherwise, it evaluates the FirstName field (see Figure 11.23).
FIGURE 11.23 A SELECT statement that uses the COALESCE function to appropriately handle nulls in the Title and MiddleName fields.
Summary
The T-SQL language provides you with a rich function library. Using this rich library of T-SQL functions, you can manipulate your data in ways you probably haven’t even dreamed of. In this lesson, you learned many of the commonly used numeric, string, and date-time functions. You also learned how functions can help you with the process of handling null values in your data.
Q&A
Q. Explain what the STUFF function does.
A. The STUFF function starts at a certain position and replaces a specified number of characters with other specified characters.
Q. Explain what the DATEPART function does.
A. The DATEPART function extracts part of a date. You designate the part of the date you want to extract and the date from which you want to extract it.
Q. Explain the ISNULL function.
A. The ISNULL function returns information about whether the value in an expression is null. It receives the expression you want to evaluate and the value you want to return if the expression is null. It returns the specified value.
Workshop
Quiz
1. What function extracts specified characters from a string?
2. The NOW function returns the system date and time (true/false).
3. Name the two parameters to the DATEPART function.
4. What function finds the difference between two dates?
5. The NULLIF function determines whether a value is null (true/false).
Quiz Answers
1. The SUBSTRING function extracts specified characters from a string.
2. False. The GETDATE function returns the system date and time.
3. The first parameter is an abbreviation designating the part of the date you want to extract. The second parameter is the date from which you want to extract it.
4. The DATEDIFF function.
5. False. The ISNULL function determines whether a value is null.
Activities
Practice executing T-SQL statements that contain functions. First, find the leftmost four characters of each person’s LastName in the Person.Person table of the AdventureWorks2014 database. Next, find the fourth through the eighth characters of the AddressLine1 field in the Person.Address table. Find the rightmost four characters of the AddressLine1 field. Next replace all occurrences of the AddressLine1 of “Street” with the word “Court.” Find the month of each ModifiedDate in the Person.Person table. Then extract the quarter of each ModifiedDate in the Person.Address table. Find the difference in days between the StartDate and the EndDate in the Production.ProductCostHistory table. Finally, use a SELECT statement to display “No Address2” for all rows in the Person.Address table where the AddressLine2 field is null.