Functions

Functions are operations that are usually performed on data, usually to facilitate conversion and manipulation. You can use expressions in the SELECT, WHERE, and ORDER BY clauses as well as in other clauses

Using Functions

SQL supports a lot of functions, they are functions to handle the following :

  • Text
  • Numbers
  • Dates
  • System

Text Functions

These functions are used to manipulate text

  • UPPER
  • LOWER
  • RTRIM
  • LTRIM
  • TRIM
  • SUBSTRING
  • LENGTH

UPPER and LOWER

Use UPPER and LOWER to change a string to either uppercase or lowercase. You may need to display all uppercase data in a report, for example. The syntax is very simple.

UPPER(<string>) LOWER(<string>)

USE AdventureWorks2012;
GO
SELECT LastName, UPPER(LastName) AS "UPPER",
    LOWER(LastName) AS "LOWER"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Case sensitivity when searching

You may think that you will use UPPER or LOWER often in the WHERE clause to make sure that the case of the value does not affect the results, but usually you don’t need to do this. By default, searching in T-SQL is case insensitive. The collation of the column determines whether the search will be case sensitive. This is defined at the server, but you can specify a different collation of the database, table, or column

RTRIM and LTRIM

The RTRIM and LTRIM functions remove spaces from the right side (RTRIM) or left side (LTRIM) of a string.

 Using RTRIM and LTRIM

--Create the temp table
CREATE TABLE #trimExample (COL1 VARCHAR(10));
GO
--Populate the table
INSERT INTO #trimExample (COL1)
VALUES ('a  '),('b  '),('  c'),('  d  ');

--Select the values using the functions
SELECT COL1, '*' + RTRIM(COL1) + '*' AS "RTRIM",
    '*' + LTRIM(COL1) + '*' AS "LTRIM"
FROM #trimExample;

--Clean up
DROP TABLE #trimExample;

Analysis

The RTRIM function removed the spaces from the right side; the LTRIM function removed the spaces from the left side. T-SQL doesn’t contain a native function that removes the spaces from both sides of the string

LEFT and RIGHT

The LEFT and RIGHT functions return a specified number of characters on the left or right side of a string. Developers use these functions to parse strings. For example, you may need to retrieve the three-character extension from file path data by using RIGHT. Take a look at the syntax.

LEFT(<string>,<number of characters) RIGHT(<string>,<number of characters)

USE AdventureWorks2012;
GO
SELECT LastName,LEFT(LastName,5) AS "LEFT",
    RIGHT(LastName,4) AS "RIGHT"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Analysis

Notice that even if the value contains fewer characters than the number specified in the second parameter, the function still works to return as many characters as possible.

LEN and DATALENGTH

Use LEN to return the number of characters in a string. Developers sometimes use another function, DATALENGTH, incorrectly in place of LEN. DATALENGTH returns the number of bytes in a string. The problem occurs when using DATALENGTH on NCHAR or NVARCHAR data types, which take two byes per characters. In this case, the DATALENGTH value is two times the LEN value.

The syntax is very simple.

LEN(<string>) DATALENGTH(<string>)

USE AdventureWorks2012;
GO

SELECT LastName,LEN(LastName) AS "Length",
    DATALENGTH(LastName) AS "Data Length"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

CHARINDEX

Use CHARINDEX to find the numeric starting position of a search string inside another string. By checking to see whether the value returned by CHARINDEX is greater than zero, you can use the function to just determine whether the search string exists inside the second value.

The following is the syntax; remember that the third parameter is optional (square brackets surround optional parameters in the syntax):

CHARINDEX(<search string>,<target string>[,<start location>])

USE AdventureWorks2012;
GO
SELECT LastName, CHARINDEX('e',LastName) AS "Find e",
    CHARINDEX('e',LastName,4) AS "Skip 4 Characters",
    CHARINDEX('be',LastName) AS "Find be",
    CHARINDEX('Be',LastName) AS "Find B"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Analysis

The Skip 4 Characters column displays the first location of the letter e when the first four characters of the LastName value are ignored. Finally, the Find be column demonstrates that you can use the function with search strings that are more than one character in length. Notice how “Be” returns a value of “Bell”. This is due to the case sensitivity of the AdventureWorks2012 database, which differentiates between an upper and lowercase “b.”

SUBSTRING

Use SUSTRING to return a portion of a string starting at a given position and for a specified number of characters. For example, an order-entry application may assign a customer ID based on the first seven letters of the customer’s last name plus digits 4–9 of the phone number. The SUBSTRING function requires three parameters: the string, a starting location, and the number of characters to retrieve.

Here is the syntax of SUBSTRING:

SUBSTRING(<string>,<start location>,<length>)

USE AdventureWorks2012;
GO
SELECT LastName, SUBSTRING(LastName,1,4) AS "First 4",
    SUBSTRING(LastName,5,50) AS "Characters 5 and later"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Analysis

Notice in the results that if the starting point is located after the available characters (Abel and Bell), an empty string is returned.

REVERSE

REVERSE returns a string in reverse order.

SELECT REVERSE('!dlroW ,olleH')

REPLACE

Use REPLACE to substitute one string value for another. REPLACE has three required parameters, but it is very easy to use. Use REPLACE to clean up data; for example, you may need to replace slashes (/) in a phone number column with hyphens (-) for a report. Here is the syntax:

REPLACE(<string value>,<string to replace>,<replacement>)

USE AdventureWorks2012;
GO

--1
SELECT LastName, REPLACE(LastName,'A','Z') AS "Replace A",
    REPLACE(LastName,'A','ZZ') AS "Replace with 2 characters",
    REPLACE(LastName,'ab','') AS "Remove string"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

--2
SELECT BusinessEntityID,LastName,MiddleName,
    REPLACE(LastName,'a',MiddleName) AS "Replace with MiddleName",
    REPLACE(LastName,MiddleName,'a') AS "Replace MiddleName"
FROM Person.Person
WHERE BusinessEntityID IN (285,293,10314);

Nesting Functions

The previous section showed how to use one function at a time to manipulate strings. If the results of one expression must be used as a parameter of another function call, you can nest functions. For example, you can nest the LTRIM and RTRIM functions to remove the spaces from the beginning and ending of a string like this: LTRIM(RTRIM(' test ')).

USE AdventureWorks2012;
GO

--1
SELECT EmailAddress,
    SUBSTRING(EmailAddress,CHARINDEX('@',EmailAddress) + 1,LEN(EmailAddress)) AS DOMAIN
FROM Production.ProductReview;

--2
SELECT EmailAddress,
RIGHT(EmailAddress, CHARINDEX('@', REVERSE(EmailAddress))-1) AS DOMAIN
FROM Production.ProductReview;

Exercise

Use the AdventureWorks2012 database to complete this exercise.

  1. Write a query that displays the first 10 characters of the AddressLine1 column in the Person.Address table.

  2. Write a query that displays characters 10 to 15 of the AddressLine1 column in the Person.Address table.

  3. Write a query displaying the first and last names from the Person.Person table all in uppercase.

  4. The ProductNumber in the Production.Product table contains a hyphen (-). Write a query that uses the SUBSTRING function and the CHARINDEX function to display the characters in the product number following the hyphen. Note: there is also a second hyphen in many of the rows; ignore the second hyphen for this question. Hint: Try writing this statement in two steps, the first using the CHARINDEX function and the second adding the SUBSTRING function.

Date and Time Functions

Just as T-SQL features a rich set of functions for working with string data, it also boasts an impressive list of functions for working with date and time data types.

GETDATE and SYSDATETIME

Use GETDATE or SYSDATETIME to return the current date and time of the server. The difference is that SYSDATETIME returns seven decimal places after the second, while GETDATE returns only three places. GETDATE and SYSDATETIME are nondeterministic functions. This means that they return different values each time they are called.

SELECT GETDATE(), SYSDATETIME();

DATEADD

Use DATEADD to add a number of time units to a date. The function requires three parameters: the date part, the number, and a date. T-SQL doesn’t have a DATESUBTRACT function, but you can use a negative number to accomplish the same thing. You might use DATEADD to calculate an expiration date or a date that a payment is due.

Here is the syntax for DATEADD:

DATEADD(<date part>,<number>,<date>)

Use AdventureWorks2012
GO
--1
SELECT OrderDate, DATEADD(year,1,OrderDate) AS OneMoreYear,
    DATEADD(month,1,OrderDate) AS OneMoreMonth,
    DATEADD(day,-1,OrderDate) AS OneLessDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
--2
SELECT DATEADD(month,1,'1/29/2009') AS FebDate;

Note

Notice in the results of query 2 that since there is no 29th day of February 2009, adding one month to January 29, 2009, returns February 28, the last possible day in February that year.

DATEDIFF

The DATEDIFF function allows you to find the difference between two dates. The function requires three parameters: the date part and the two dates. The DATEDIFF function might be used to calculate how many days have passed since unshipped orders were taken, for example. Here is the syntax:

DATEDIFF(<datepart>,<early date>,<later date>)

Use AdventureWorks2012;
GO

--1
SELECT OrderDate, GETDATE() CurrentDateTime,
    DATEDIFF(year,OrderDate,GETDATE()) AS YearDiff,
    DATEDIFF(month,OrderDate,GETDATE()) AS MonthDiff,
    DATEDIFF(day,OrderDate,GETDATE()) AS DayDiff
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

--2
SELECT DATEDIFF(year,'12/31/2008','1/1/2009') AS YearDiff,
    DATEDIFF(month,'12/31/2008','1/1/2009') AS MonthDiff,
    DATEDIFF(day,'12/31/2008','1/1/2009') AS DayDiff;

Analysis

Even though query 2 compares the difference between two dates that are just one day apart, the differences in years and months are both 1. The DATEDIFF rounds up the result to the nearest integer and doesn’t display decimal results.

DATENAME and DATEPART

The DATENAME and DATEPART functions return the part of the date specified. Developers use the DATENAME and DATEPART functions to display just the year or month on reports, for example. DATEPART always returns a numeric value. DATENAME returns the actual name when the date part is the month or the day of the week.

The syntax for the two functions is similar.

DATENAME(<datepart>,<date>) DATEPART(<datepart>,<date>)

Use AdventureWorks2012
GO
--1
SELECT OrderDate, DATEPART(year,OrderDate) AS OrderYear,
    DATEPART(month,OrderDate) AS OrderMonth,
    DATEPART(day,OrderDate) AS OrderDay,
    DATEPART(weekday,OrderDate) AS OrderWeekDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

--2
SELECT OrderDate, DATENAME(year,OrderDate) AS OrderYear,
    DATENAME(month,OrderDate) AS OrderMonth,
    DATENAME(day,OrderDate) AS OrderDay,
    DATENAME(weekday,OrderDate) AS OrderWeekDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

Analysis

You will see that the results are the same except for spelling out the month and weekday in query 2. One other thing to keep in mind is that the value returned from DATEPART is always an integer, while the value returned from DATENAME is always a string, even when the expression returns a number.

DAY, MONTH, and YEAR

The DAY, MONTH, and YEAR functions work just like DATEPART. These functions are just alternate ways to get the day, month, or year from a date. Here is the syntax:

DAY(<date>) MONTH(<date>) YEAR(<date>)

Use AdventureWorks2012
GO

SELECT OrderDate, YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    DAY(OrderDate) AS OrderDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

CONVERT

The CONVERT function has an optional parameter called style that can be used to format a date.

I have frequently seen code that used the DATEPART function to break a date into its parts and then cast the parts into strings and concatenate them back together to format the date. It is so much easier just to use CONVERT to accomplish the same thing! Here is the syntax:

CONVERT(<data type, usually varchar>,<date>,<style>)

Using CONVERT to Format a Date/Time Value

--1 The hard way!
SELECT CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '/' +
    CAST(DATEPART(MM,GETDATE()) AS VARCHAR) +
    '/' +  CAST(DATEPART(DD,GETDATE()) AS VARCHAR) AS DateCast;
--2 The easy way!
SELECT CONVERT(VARCHAR,GETDATE(),111) AS DateConvert;
--3
USE AdventureWorks2012
GO
SELECT CONVERT(VARCHAR,OrderDate,1) AS "1",
    CONVERT(VARCHAR,OrderDate,101) AS "101",
    CONVERT(VARCHAR,OrderDate,2) AS "2",
    CONVERT(VARCHAR,OrderDate,102) AS "102"
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

Take a look at the SQL Server Books Online article “CAST and CONVERT” for a list of all the possible formats.

FORMAT

SQL Server 2012 introduces the FORMAT function. The primary purpose is to simplify the conversion of date/time values as string values. Another purpose of the format function is to convert date/time values to their cultural equivalencies. Here is the syntax:

FORMAT ( value, format [, culture ] ) The FORMAT function greatly simplifies how date/time values are converted, and it should be used for date/time values instead of the CAST or CONVERT functions

SELECT FORMAT( GETDATE(), 'dd', 'en-US' ) AS Result;
SELECT FORMAT( GETDATE(), 'd/M/y', 'en-US' ) AS Result;
SELECT FORMAT( GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS Result;

SQL 2012 also introduces a simple method to derive a date, time, or date and time from a list of values. The primary function is called DATEFROMPARTS but there is also a version of the function for time, and date and time

SELECT DATEFROMPARTS(2012, 3, 10) AS RESULT;
SELECT TIMEFROMPARTS(12, 10, 32, 0, 0) AS RESULT;
SELECT DATETIME2FROMPARTS (2012, 3, 10, 12, 10, 32, 0, 0) AS RESULT;

Exercise

Use the AdventureWorks2012 database to complete this exercise.

  1. Write a query that calculates the number of days between the date an order was placed and the date that it was shipped using the Sales.SalesOrderHeader table. Include the SalesOrderID, OrderDate, and ShipDate columns.

  2. Write a query that displays only the date, not the time, for the order date and ship date in the Sales.SalesOrderHeader table.

  3. Write a query that adds six months to each order date in the Sales.SalesOrderHeader table. Include the SalesOrderID and OrderDate columns.

  4. Write a query that displays the year of each order date and the numeric month of each order date in separate columns in the results. Include the SalesOrderID and OrderDate columns.

  5. Change the query written in question 4 to display the month name instead.

Concatenating Strings

The concatenation operator (+) allows you to add together two strings. The syntax is simple:<string or column name> + <string or column name>.

USE AdventureWorks2012;
GO

--1
SELECT 'ab' + 'c';

--2
SELECT BusinessEntityID, FirstName + ' ' + LastName AS "Full Name"
FROM Person.Person;
--3
SELECT BusinessEntityID, LastName + ', ' + FirstName AS "Full Name"
FROM Person.Person;

Concatenating Strings and NULL

When concatenating a string with a NULL, NULL is returned. The following demonstrates the problem :

USE AdventureWorks2012;
GO

SELECT BusinessEntityID, FirstName + ' ' + MiddleName +
    ' ' + LastName AS "Full Name"
FROM Person.Person;

Analysis

The query combines the FirstName, MiddleName, and LastName columns into a Full Name column. The MiddleName column is optional; that is, NULL values are allowed. Only the rows where the MiddleName value has been entered show the expected results. The rows where MiddleName is NULL return NULL.

CONCAT

SQL 2012 introduces another powerful tool for concatenating strings. The CONCAT statement takes any number of strings as arguments and automatically concatenates them together. The values can be passed to the CONCAT statement as variables or as regular strings. The output is always implicitly converted to a string datatype.

-- Simple CONCAT statement
SELECT CONCAT ('I ', 'love', ' writing', ' T-SQL') AS RESULT;

--Using variable with CONCAT
DECLARE @a VARCHAR(30) = 'My birthday is on '
DECLARE @b DATE = '08/25/1980'
SELECT CONCAT (@a, @b) AS RESULT;

--Using CONCAT with table rows
USE AdventureWorks2012
SELECT CONCAT (AddressLine1, PostalCode) AS Address
FROM Person.Address;

ISNULL and COALESCE

Two functions are available to replace NULL values with another value. The first function, ISNULL, requires two parameters: the value to check and the replacement for NULL values. COALESCE works a bit differently. COALESCE will take any number of parameters and return the first non-NULL value. T-SQL developers often prefer COALESCE over ISNULL because COALESCE meets ANSI standards, while ISNULL does not. Also, COALESCE is more versatile. Here is the syntax for the two functions:

ISNULL(<value>,<replacement>)
COALESCE(<value1>,<value2>,...,<valueN>)

Here is an example :

USE AdventureWorks2012;
GO

--1
SELECT BusinessEntityID, FirstName + ' ' + ISNULL(MiddleName,'') +
    ' ' + LastName AS "Full Name"
FROM Person.Person;
--2
SELECT BusinessEntityID, FirstName + ISNULL(' ' + MiddleName,'') +
    ' ' + LastName AS "Full Name"
FROM Person.Person;

--3
SELECT BusinessEntityID, FirstName + COALESCE(' ' + MiddleName,'') +
    ' ' + LastName AS "Full Name"
FROM Person.Person

Query 1 uses the ISNULL function to replace any missing MiddleName values with an empty string in order to build Full Name.

Concatenating Other Data Types to Strings

To concatenate nonstring values to strings, the nonstring value must be converted to a string. If the string value can be implicitly converted to a number, the values will be added together instead. Run this statement to see what happens: SELECT 1 + '1';. If the desired result is 11 instead of 2, the numeric value must be converted to a string using either the CAST or CONVERT function.

Use one of the functions, CAST or CONVERT, to convert a numeric or temporal value to a string. Here is the syntax:

CAST(<value> AS <new data type>)
CONVERT(<new data type>,<value>)

Here is an example of using them :

USE AdventureWorks2012
GO

--1
SELECT CAST(BusinessEntityID AS NVARCHAR) + ': ' + LastName
    + ', ' + FirstName AS ID_Name
FROM Person.Person;

--2
SELECT CONVERT(NVARCHAR(10),BusinessEntityID) + ': ' + LastName
    + ', ' + FirstName AS ID_Name
FROM Person.Person;

--3
SELECT BusinessEntityID, BusinessEntityID + 1 AS "Adds 1",
    CAST(BusinessEntityID AS NVARCHAR(10)) + '1'AS "Appends 1"
FROM Person.Person;

The functions in queries 1 and 2 have very different syntaxes, but they accomplish the same result.. They both change the BusinessEntityID values from integers into a string data type (NVARCHAR) so that it can be concatenated to a string. Many programmers prefer CAST over CONVERT because CAST is compliant with the ANSI SQL-99 standard.

Exercise

  1. Write a query that returns data from the Person.Address table in this format AddressLine1 (City PostalCode) from the Person.Address table.

  2. Write a query using the Production.Product table displaying the_ product ID, color, and name_ columns. If the color column contains a NULL value, replace the color with No Color.

  3. Modify the query written in question 2 so that the description of the product is returned formatted as Name: Color. Make sure that all rows display a value even if the Color value is missing.

  4. Write a query using the Production.Product table displaying a description with the ProductID: Name format. Hint: You will need to use a function to write this query.

  5. Explain the difference between the ISNULL and COALESCE functions.

Using Mathematical Operators

You can use several operators to perform simple mathematical operations on numeric values. Use the plus symbol (+) to perform addition, the minus symbol (–) to perform subtraction, the asterisk (*) to perform multiplication, and the slash (/) to perform division. Use the modulo operator (%) to perform remainder division.

--1
SELECT 1 + 1 AS ADDITION, 10.0 / 3 AS DIVISION, 10 / 3 AS [Integer Division], 10 % 3 AS MODULO;

--2
SELECT OrderQty, OrderQty * 10 AS Times10
FROM Sales.SalesOrderDetail;

--3
SELECT OrderQty * UnitPrice * (1.0 - UnitPriceDiscount)
    AS Calculated, LineTotal
FROM Sales.SalesOrderDetail;

--4
SELECT SpecialOfferID,MaxQty,DiscountPct,
    DiscountPct * ISNULL(MaxQty, 1000) AS MaxDiscount
FROM Sales.SpecialOffer;

Analysis

Note that 10 / 3 does not equal 3.333. Because the two operators in that expression are integers, the result is also an integer. Query 2 shows the result of multiplying the values stored in the OrderQty column by 10.

Exercise

Use the AdventureWorks database to complete this exercise. You can find the solutions at the end of the chapter.

  1. Write a query using the_ Sales.SpecialOffer_ table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.

  2. Write a query using the Sales.SpecialOffer table. Multiply the MinQty column by the DiscountPct column. Include the SpecialOfferID and Description columns in the results.

  3. Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.

  4. Describe the difference between division and modulo.

Solution

Use the AdventureWorks database to complete this exercise.

Question 1

Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.

SELECT SpecialOfferID, Description,
     MaxQty - MinQty AS Diff 
FROM Sales.SpecialOffer;

Question 2

Write a query using the Sales.SpecialOffer table. Multiply the MinQty column by the DiscountPct column. Include the SpecialOfferID and Description columns in the results.

SELECT SpecialOfferID, Description, MinQty * DiscountPct AS Discount 
FROM Sales.SpecialOffer;

Question 3

Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.

SELECT SpecialOfferID, Description,     ISNULL(MaxQty,10) * DiscountPct AS Discount 
FROM Sales.SpecialOffer;

Question 4

Describe the difference between division and modulo.

When performing division, you divide two numbers, and the result, the quotient, is the answer. If you are using modulo, you divide two numbers, but the reminder is the answer. If the numbers are evenly divisible, the answer will be zero.

Mathematical Functions

ABS

The ABS function returns the absolute value of the number—the difference between the number and zero. Type in and execute this code to see how to use ABS:

SELECT ABS(2) AS "2", ABS(-2) AS "-2"

POWER

The POWER function returns the power of one number to another number. The syntax is simple.

POWER(<number>,<power>)

There may not be many uses for POWER in business applications, but you may use it in scientific or academic applications.

SELECT POWER(10,1) AS "Ten to the First",
    POWER(10,2) AS "Ten to the Second",
    POWER(10,3) AS "Ten to the Third";

The POWER function returns a FLOAT value. Caution must be taken, however, with this function. The results will increase in size very quickly and can cause an overflow error.

SQUARE and SQRT

The SQUARE function returns the square of a number, or the number multiplied to itself. The SQRT function returns the opposite, the square root of a number.

SELECT SQUARE(10) AS "Square of 10",
    SQRT(10) AS "Square Root of 10",
    SQRT(SQUARE(10)) AS "The Square Root of the Square of 10";

ROUND

The ROUND function allows you to round a number to a given precision. The ROUND function is used frequently to display only the number of decimal places required in the report or application. The ROUND function requires two parameters, the number and the length, which can be either positive or negative. It also has an optional third parameter that causes the function to just truncate instead of rounding if a nonzero value is supplied. Here is the syntax:

ROUND(<number>,<length>[,<function>])

SELECT ROUND(1234.1294,2) AS "2 places on the right",
    ROUND(1234.1294,-2) AS "2 places on the left",
    ROUND(1234.1294,2,1) AS "Truncate 2",
    ROUND(1234.1294,-2,1) AS "Truncate -2";

When the expression contains a negative number as the second parameter, the function rounds on the left side of the decimal point. Notice the difference when 1 is used as the third parameter, causing the function to truncate instead of rounding. When rounding 1234.1294, the expression returns 1234.1300. When truncating 1234.1294, the expression returns 1234.1200. It doesn’t round the value; it just changes the specified digits to zero.

RAND

RAND returns a float value between 0 and 1. RAND can be used to generate a random value. This might be used to generate data for testing an application, for example. The RAND function takes one optional integer parameter, @seed. When the RAND expression contains the seed value, the function returns the same value each time. If the expression doesn’t contain a seed value, SQL Server randomly assigns a seed, effectively providing a random number.

SELECT CAST(RAND() * 100 AS INT) + 1 AS "1 to 100",
    CAST(RAND()* 1000 AS INT) + 900 AS "900 to 1900",
    CAST(RAND() * 5 AS INT)+ 1 AS "1 to 5";

If you supply a seed value to one of the calls to RAND within a batch of statements, that seed affects the other calls. The value is not the same, but the values are predictable. Run this statement several times to see what happens when a seed value is used:

SELECT RAND(3),RAND(),RAND();

If you leave out the seed, you will see different results each time. Another interesting thing is that, even though you see different values in each column, you will see the same values in each row. Run this statement multiple times to see what happens:

SELECT RAND(),RAND(),RAND(),RAND()
FROM sys.objects;

Exercise

Use the AdventureWorks2012 database to complete this exercise.

  1. Write a query using the Sales.SalesOrderHeader table that displays the SubTotal rounded to two decimal places. Include the SalesOrderID column in the results.

  2. Modify the query from question 1 so that the SubTotal is rounded to the nearest dollar but still displays two zeros to the right of the decimal place.

  3. Write a query that calculates the square root of the SalesOrderID value from the Sales.SalesOrderHeader table.

  4. Write a statement that generates a random number between 1 and 10 each time it is run.

Logical Functions and Expressions

T-SQL contains a number of functions and the CASE expression that allow you to add conditional expressions to a query. You can return a value that depends on another value or the results of an expression.

Use the CASE expression to evaluate a list of expressions and return the first one that evaluates to true. For example, a report may need to display the season of the year based on one of the date columns in the table.

There are two ways to write a CASE expression: simple or searched.

Simple CASE

To write the simple CASE expression, come up with an expression that you want to evaluate, often a column name, and a list of possible values. Here is the syntax:

CASE <test expression>
    WHEN <comparison expression1> THEN <return value1>
    WHEN <comparison expression2> THEN <return value2>
    [ELSE <value3>] END

Here is an example of using the case statement :

SELECT Title,
    CASE Title
    WHEN 'Mr.' THEN 'Male'
    WHEN 'Ms.' THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Miss' THEN 'Female'
    ELSE 'Unknown' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);

Even though the CASE expression took up a lot of room in the query, it is producing only one column in the results. For each row returned, the expression evaluates the Title column to see whether it matches any of the possibilities listed and returns the appropriate value. If the value from Title doesn’t match or is NULL, then whatever is in the ELSE part of the expression is returned. If no ELSE exists, the expression returns NULL.

Searched CASE

Developers often use the searched CASE syntax when the expression is too complicated for the simple CASE syntax. For example, you might want to compare the value from a column to several values in an IN list or use greater-than or less-than operators. The CASE expression returns the first expression that returns true. This is the syntax for the searched CASE:

CASE WHEN <test expression1> THEN <value1>
[WHEN <test expression2> THEN <value2>]
[ELSE <value3>] END

Here is the same query re-written using the Search Case statement :

SELECT Title,
    CASE WHEN Title IN ('Ms.','Mrs.','Miss') THEN 'Female'
    WHEN Title = 'Mr.' THEN 'Male'
    ELSE 'Unknown' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);

The CASE expression evaluates each WHEN expression independently until finding the first one that returns true. It then returns the appropriate value. If none of the expressions returns true, the function returns the value from the ELSE part or NULL if no ELSE is available.

Note the return value

The data type returned from the CASE statement needs to be the same otherwise you will get an error.

Try the following and notice you get an error because of the different data types :

SELECT Title,
    CASE WHEN Title IN ('Ms.','Mrs.','Miss') THEN 1
    WHEN Title = 'Mr.' THEN 'Male'
    ELSE '1' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);

Listing a Column as the Return Value

It is also possible to list a column name instead of hard-coded values in the THEN part of the CASE expression. This means that you can display one column for some of the rows and another column for other rows.

SELECT VacationHours,SickLeaveHours,
    CASE WHEN VacationHours > SickLeaveHours THEN VacationHours
    ELSE SickLeaveHours END AS 'More Hours'
FROM HumanResources.Employee;

If there are more VacationHours than SickLeaveHours, the query displays the VacationHours column from the HumanResources.Employee table in the More Hours column. Otherwise, the query returns the SickLeaveHours.

IIF

SQL Server 2012 introduced one easier method of writing a simple CASE expression. Starting with SQL Server 2012 you can now use an IIF function to return a result based on whether a Boolean expression is true or false. Here is the basic syntax for the IIF function:

IIF ( boolean_expression, true_value, false_value )

Here is an example of using the IIF function

--1 IIF function without variables
SELECT IIF (50 > 20, 'TRUE', 'FALSE') AS RESULT;

--2 IIF function with variables
DECLARE @a INT = 50
DECLARE @b INT = 25
SELECT IIF (@a > @b, 'TRUE', 'FALSE') AS RESULT;

COALESCE

You can use COALESCE with other data types as well and with any number of arguments to return the first non-NULL value. You can use the COALESCE function in place of ISNULL. If a list of values must be evaluated instead of one value, you must use COALESCE instead of ISNULL. COALESCE may be used when concatenating strings or any time that a replacement for NULL must be found.

SELECT ProductID,Size, Color,
    COALESCE(Size, Color,'No color or size') AS 'Description'
FROM Production.Product
where ProductID in (1,2,317,320,680,706);

Exercise

  1. Write a query using the HumanResources.Employee table to display the BusinessEntityID column. Also include a CASE expression that displays “Even” when the BusinessEntityID value is an even number or “Odd” when it is odd. Hint: Use the modulo operator.

  2. Write a query using the Sales.SalesOrderDetail table to display a value (“Under 10” or “10–19” or “20–29” or “30–39” or “40 and over”) based on the OrderQty value by using the CASE expression. Include the SalesOrderID and OrderQty columns in the results.

  3. Using the Person.Person table, build the full names using the Title, FirstName, MiddleName, LastName, and Suffix columns. Check the table definition to see which columns allow NULL values and use the COALESCE function on the appropriate columns.

  4. Look up the SERVERPROPERTY function in Books Online. Write a statement that displays the edition, instance name, and machine name using this function.

Solution

1.

Write a query using the HumanResources.Employee table to display the BusinessEntityID column. Also include a CASE expression that displays “Even” when the BusinessEntityID value is an even number or “Odd” when it is odd. Hint: Use the modulo operator.

SELECT BusinessEntityID,
     CASE BusinessEntityID % 2
     WHEN 0 THEN 'Even' ELSE 'Odd' END 
FROM HumanResources.Employee;

2.

Write a query using the Sales.SalesOrderDetail table to display a value (“Under 10” or “10–19” or “20–29” or “30–39” or “40 and over”) based on the OrderQty value by using the CASE expression. Include the SalesOrderID and OrderQty columns in the results.

SELECT SalesOrderID, OrderQty,
     CASE WHEN OrderQty BETWEEN 0 AND 9
            THEN 'Under 10'
        WHEN OrderQty BETWEEN 10 AND 19
            THEN '10-19'
        WHEN OrderQty BETWEEN 20 AND 29
            THEN '20-29'
        WHEN OrderQty BETWEEN 30 AND 39
            THEN '30-39'
        ELSE '40 and over' end AS range 
FROM Sales.SalesOrderDetail;

3.

Using the Person.Person table, build the full names using the Title, FirstName, MiddleName, LastName, and Suffix columns. Check the table definition to see which columns allow NULL values and use the COALESCE function on the appropriate columns.

SELECT COALESCE(Title + ' ','') + FirstName +
     COALESCE(' ' + MiddleName,'') + ' ' + LastName +
     COALESCE(', ' + Suffix,'') 
FROM Person.Person;

4.

Look up the SERVERPROPERTY function in Books Online. Write a statement that displays the edition, instance name, and machine name using this function.

SELECT SERVERPROPERTY('Edition'),
     SERVERPROPERTY('InstanceName'),
     SERVERPROPERTY('MachineName');

Administrative Functions

T-SQL contains many administrative functions that are useful for developers.

Here are some system functions :

SELECT 
    @@VERSION 'Database Version',
    DB_ID() 'Database ID',
    DB_NAME() AS "Database Name",
    HOST_NAME() AS "Host Name",
    CURRENT_USER AS "Current User",
    SUSER_NAME() AS "Login",
    USER_NAME() AS "User Name",
    ORIGINAL_LOGIN() 'Original Login',
    USER 'User',
    SYSTEM_USER as 'System User',
    APP_NAME() AS "App Name";

Using Functions in the WHERE and ORDER BY Clauses

So far you have seen functions used in the SELECT list. You may also use functions in the WHERE and ORDER BY clauses.

--1
SELECT FirstName
FROM Person.Person
WHERE CHARINDEX('ke',FirstName) > 0;

--2
SELECT LastName,REVERSE(LastName)
FROM Person.Person
ORDER BY REVERSE(LastName);

--3
SELECT BirthDate
FROM HumanResources.Employee
ORDER BY YEAR(BirthDate);

Even though it is very easy to use a function on a column in the WHERE clause, it is important to note that performance may suffer. If the database designer created an index on the searched column, the database engine must evaluate each row one at a time when a function is applied to a column. It still may search the column in the index, one value at a time, which could still be better than searching every row of the table.

Exercise

Use the AdventureWorks database to complete this exercise. You will find the solutions to the questions at the end of the chapter.

  1. Write a query using the Sales.SalesOrderHeader table to display the orders placed during 2005 by using a function. Include the SalesOrderID and OrderDate columns in the results.

  2. Write a query using the Sales.SalesOrderHeader table listing the sales in order of the month the order was placed and then the year the order was placed. Include the SalesOrderID and OrderDate columns in the results.

  3. Write a query that displays the PersonType and the name columns from the Person.Person table. Sort the results so that rows with a PersonType of IN, SP, or SC sort by LastName. The other rows should sort by FirstName. Hint: Use the CASE expression

Solution

Use the AdventureWorks database to complete this exercise.

1.

Write a query using the Sales.SalesOrderHeader table to display the orders placed during 2005 by using a function. Include the SalesOrderID and OrderDate columns in the results.

--one possible solution. 
SELECT SalesOrderID, OrderDate 
FROM Sales.SalesOrderHeader 
WHERE YEAR(OrderDate) = 2005;

2.

Write a query using the Sales.SalesOrderHeader table listing the sales in order of the month the order was placed and then the year the order was placed. Include the SalesOrderID and OrderDate columns in the results.

SELECT SalesOrderID, OrderDate 
FROM Sales.SalesOrderHeader 
ORDER BY MONTH(OrderDate), YEAR(OrderDate);

3.

Write a query that displays the PersonType and the name columns from the Person.Person table. Sort the results so that rows with a PersonType of IN, SP, or SC sort by LastName. The other rows should sort by FirstName. Hint: Use the CASE expression.

SELECT PersonType, FirstName, MiddleName, LastName 
FROM Person.Person 
ORDER BY CASE WHEN PersonType IN ('IN','SP','SC')
      THEN LastName ELSE FirstName END;

The TOP Keyword

Use the TOP keyword to limit the number or percentage of rows returned from a query. Here is the syntax:

SELECT TOP(<number>) [PERCENT] [WITH TIES] <col1>,<col2>
FROM <table1> [ORDER BY <col1>]

The ORDER BY clause is optional, but most of the time, you will use it to determine which rows the query returns when using TOP. Sometimes you will see the same rows returned over and over even when not using an ORDER BY.

--1
DECLARE @Rows INT = 2;
SELECT TOP(@Rows) PERCENT CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

--2
SELECT TOP(2) CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;

--3
SELECT TOP(2) WITH TIES CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;

--4
SELECT TOP(2) CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY NEWID();

Query 1 shows that you can use a variable along with TOP. This has been possible since version 2005 of SQL Server. It also demonstrates the use of the PERCENT option. Query 2 is a typical example returning just two rows. Query 3 demonstrates the WITH TIES option. Otherwise, it is identical to Query 2. It returns many more rows because there are many orders placed on the same date. Query 4 demonstrates a trick to get random rows. If you sort by the NEWID function, you will get different rows each time you run the query.

Performance Exercise

You should limit the use of functions in a WHERE clause. Indexes might be used when a function is used to manipulate a column. For this exercise, lets create an index on the OrderDate column :

CREATE NONCLUSTERED INDEX [DEMO_SalesOrderHeader_OrderDate]
    ON [Sales].[SalesOrderHeader]
([OrderDate] ASC);

Run the queries and compare the results :

Compare the Performance When Using a Function in the WHERE Clause

--1
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2005-01-01 00:00:00'
    AND OrderDate <= '2006-01-01 00:00:00';

--2
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2005;

Query 1 finds all the orders placed in 2005 without using a function. Query 2 uses the YEAR function to return the same results.

Drop the index :

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
    OBJECT_ID(N'[Sales].[SalesOrderHeader]')
    AND name = N'DEMO_SalesOrderHeader_OrderDate')
DROP INDEX [DEMO_SalesOrderHeader_OrderDate]
ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF );

Run again now that the index is gone. From the query plan the performance is almost identical. Now the database engine must perform a scan of the table (in this case, the clustered index) to find the correct rows in both of the queries.