T-SQL tricks for SQL Server 2012: T-SQL functions

In this second in a series of tips about new T-SQL features in SQL Server 2012, expert Robert Sheldon explores more tricks for getting the most out of T-SQL functions.

Editor’s note: This is the second installment in a series about new T-SQL features in SQL Server 2012. Part one discusses The joy of data values. This section discusses more T-SQL functions involving data values.

Whenever Microsoft releases a new version of SQL Server, the new version inevitably contains enhancements to Transact-SQL (T-SQL), as is the case with SQL Server 2012. The latest updates to T-SQL include numerous new and modified elements that extend the language’s capabilities.

TRY_CONVERT

This new function -- TRY_CONVERT -- lets you test a conversion. The function works just like TRY_PARSE, in that it returns a NULL value rather than an error if the conversion fails. Let’s look at a few examples to demonstrate how this works.

The following SELECT statement uses the CONVERT function to convert the string books to the VARCHAR data type:

SELECT CONVERT(VARCHAR(5), 'books');

 

For more about T-SQL Functions

An introductory look at T-SQL functions

T-SQL functions in metadata

As you would expect, the SELECT statement returns books, which indicates that the conversion is successful. In the next example, the SELECT statement tries to perform the same conversion by using the TRY_CONVERT function:

SELECT TRY_CONVERT(VARCHAR(5), 'books');

 

Again, because the conversion can be performed, the SELECT statement returns books. But, suppose you try to convert the word books to the INT data type, as shown in the following example:

SELECT CONVERT(INT, 'books');

 

Because it’s using the CONVERT function, the SELECT statement returns a message stating that the conversion failed. However, if you instead use the TRY_CONVERT function, as in the following example, the statement will return NULL:

SELECT TRY_CONVERT(INT, 'books');

 

As with TRY_PARSE, you can use the TRY_CONVERT function to test whether the conversion will fail and then use the results of that function to control your code’s logic.

FORMAT

Transact-SQL now includes the FORMAT function, which lets you format a value based on a .NET Framework format string. In addition, you can specify that the format being returned adheres to a .NET Framework culture, as you saw with the PARSE function.

For example, the following SELECT statement uses the FORMAT function to return the current date and time based on the default format string for the Czech culture:

SELECT FORMAT(GETDATE(), '', 'Cs-CZ');

 

The FORMAT function takes three arguments. The first is the value to be converted. In this case, that value is the one returned by the GETDATE function. The second argument is the format string. Because an empty string is specified, the default format is used. The third argument is the culture code. Once again, the Czech code is being used. As a result, the SELECT statement returns 4.6.2012 15:13:18 for the time and date value.

Let’s look closer at the second argument, the format string. In the following example, I specify that the returned value be formatted as day of week, month date, year:

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy', 'Cs-CZ');

 

Now the results look quite different: pondělí, června 04, 2012. If you change the culture code, however, those results will change once again. For example, the following SELECT statement uses the U.S. English culture code:

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy', 'en-US');

 

Now the SELECT statement returns the value Monday, June 04, 2012.

The key to using the FORMAT function is to remember that the results are based on a combination of the format string and the culture code.

CHOOSE

The CHOOSE function returns a value from a list of two or more values, based on a specified index value. The index value is a 1-based integer that’s included as the function’s first argument. The arguments that follow are the list of values.

In the following example, the final SELECT statement includes a CHOOSE function that returns the second value from a list of product names:

DECLARE @index INT = 2;

DECLARE @valA VARCHAR(30) =

  (SELECT Name FROM Production.Product WHERE ProductID = 970);

DECLARE @valB VARCHAR(30) =

  (SELECT Name FROM Production.Product WHERE ProductID = 971);

DECLARE @valC VARCHAR(30) =

  (SELECT Name FROM Production.Product WHERE ProductID = 972);

SELECT CHOOSE(@index, @valA, @valB, @valC);

 

The @index variable is set to the integer 2 and is used as the first argument to indicate that the second value should be returned from the list of values. The other variables are based on values retrieved from the AdventureWorks2012 sample database. Each of these values is a product name. Because the index is set to 2, the CHOOSE function returns the value of the @valB variable (Touring-2000 Blue, 50).

IIF

The IIF function lets you test a condition and return a value based on the results of that test. The IIF function takes three arguments: a valid Boolean expression, the value to return if the expression evaluates to true and the value to return if the expression evaluates to false. (You can think of the IIF function as shortened version of the CASE statement.)

In the following example, the final SELECT statement uses the IIF function to compare a product’s price to the average price for that category of prices:

DECLARE @ProdID INT = 970;

DECLARE @ProdPrice MONEY =

  (SELECT ListPrice FROM Production.Product WHERE ProductID = @ProdID);

DECLARE @AvgPrice MONEY =

   (SELECT AVG(ListPrice) FROM Production.Product

     WHERE ProductNumber LIKE 'bk-%');

SELECT IIF(@ProdPrice > @AvgPrice, 'true', 'false');

 

The first three statements declare variables and assign values to them. The @ProdPrice variable contains the list price of the specific product. The @AvgPrice variable contains the average list price of that category of products, as determined by the WHERE clause condition (ProductNumber LIKE 'bk-%'). The first argument in the IIF function, the Boolean expression, uses the greater than (>) comparison operator to compare @ProdPrice to @AvgPrice. If the product price is greater than the average price, the expression will evaluate to true. Otherwise, the expression will evaluate to false.

The IIF function’s second argument is the string value true. That means, if the Boolean expression evaluates to true, the string value true will be returned. Otherwise, the value of the third argument will be returned, which is the value false. In this case, the product price is less that the average price, so the IIF function returns false.

CONCAT

The CONCAT function concatenates two or more string values. If an argument is not a string value, the function implicitly converts it to a string. Even if a NULL value is passed in as one of the arguments, the function converts the value to an empty string. The CONCAT function is similar to the concatenation operator (+), except that CONCAT makes it easier to work with non-string and NULL values.

In the following SELECT statement, the CONCAT function concatenates two literals and two column values:

SELECT CONCAT(Name, ' (', ProductNumber, ')') AS NewName

FROM Production.Product

WHERE ProductID = 970;

 

Notice that each argument is a string value, whether derived from a column or a literal. In this case, the ProductName value is Touring-2000 Blue, 46, and the ProductNumber value is BK-T44U-46. Because the CONCAT operator is used, the SELECT statement returns a single value: Touring-2000 Blue, 46 (BK-T44U-46).

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Check out his blog, Slipstream.

This was first published in July 2012

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close