SQL with Manoj

SQL Server 2012 gem collection has become bigger with the addition of new items with this release. In my previous blog post [link] I’ve discussed about some of these, like:

1. The new CHOOSE() & IFF() logical functions.

2. PARSE(), TRY_CONVERT() & TRY_PARSE() conversion functions.

3. FIRST_VALUE(), LAST_VALUE(), LAG() & LEAD() Analytical functions.
 

Here I’ll discuss about new String Functions, CONCAT() & FORMAT(). I welcome both of them as they will provide much ease to the developers without depending on various workarounds and considering certain things people normally miss and end up on various goof ups.
 

–> CONCAT() function: as its name suggests helps combine values of columns into a single string value. Well you can also do this by using “+” operator and combine columns. But what about NULL values and datatype conversion. You can also do it but what if somehow you miss. This function takes care aboout all these things, NULL values and datatype conversion.

Let’s check this:

USE [AdventureWorks2012]
GO

-- Handling NULL implicitly:
SELECT TOP 10
	-- Combining columns by using + operator (NULL values are not handled):
	[FirstName] + ' ' + [MiddleName] + ' ' + [LastName] AS 'FullName',

	-- Combining columns by using + operator by handling NULL values explicitly:
	ISNULL([FirstName],'') + ' ' + ISNULL([MiddleName],'') + ' ' + ISNULL([LastName],'') AS 'FullName_with_ISNULL',

	-- Using CONCAT() function (NULL values are implicitly handled):
	CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS 'FullName_with_CONCAT'

FROM [Person].[Person]

Output:-

So, the output above lists NULL values when you combine multiple columns with NULL values. The 2nd column lists out expected results as we’ve handled NULL values manually. The 3rd column also results expected results as NULLs are getting handled implicitly by the CONCAT() function.

Now, let’s check how CONCAT() handles Datatype Conversion:

-- Combining columns of different datatypes, gives error:
SELECT TOP 10
	BusinessEntityID + LastName + EmailPromotion + ModifiedDate AS PersonDetails
FROM [Person].[Person]

Error Message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Sánchez' to data type int.

-- CONCAT() Handling Datatype conversion implicitly:
SELECT TOP 10
	CONCAT(BusinessEntityID, LastName, EmailPromotion, ModifiedDate) AS PersonDetails
FROM [Person].[Person]

Output:-

PersonDetails
1Sánchez0Feb  8 2003 12:00AM
2Duffy1Feb 24 2002 12:00AM
3Tamburello0Dec  5 2001 12:00AM
4Walters0Dec 29 2001 12:00AM
5Erickson0Jan 30 2002 12:00AM
6Goldberg0Feb 17 2002 12:00AM
7Miller2Mar  5 2003 12:00AM
8Margheim0Jan 23 2003 12:00AM
9Matthew0Feb 10 2003 12:00AM
10Raheem2May 28 2003 12:00AM

Check this Video tutorial on FORMAT() function:

–> FORMAT() function: Well I’ve used this function with only dates now and I’m quite happy with it. Now to convert a date to a different format you don’t have to CAST/CONVERT your datetime with lot of different options you don’t memorize or remember. With this new function you can format your dates or datetime values in various formats and in different langauges.

Let’s check this:

SELECT 
	GETDATE()											AS DateToday,
	FORMAT (GETDATE(), 'D')								AS DateFormatted, 
	FORMAT (GETDATE(), 'dd/MM/yyyy (MMM)')				AS Date_ddmmyyyyMON, 
	FORMAT (CAST('2012-02-01' AS DATE), 'MMM dd yyyy')	AS Date_MONddyyyy, 
	FORMAT (CAST('2012-02-01' AS DATE), 'MMMM dd yyyy') AS Date_Monthddyyyy;


DECLARE @someDate DATETIME
SET @someDate = '2012-02-01 14:54:39.300'

SELECT 
	FORMAT (@someDate, 'D', 'de')		AS DateNew_German,
	FORMAT (@someDate, 'f', 'fr')		AS DateNew_French,
	FORMAT (@someDate, 'D', 'es-US')	AS DateNew_Snanish,
	FORMAT (@someDate, 'f', 'zh')		AS DateNew_Chinese,
	FORMAT (@someDate, 'D', 'ar')		AS DateNew_Arabic,
	FORMAT (@someDate, 'f', 'hi-IN')	AS DateNew_Hindi;

Output:-

The 3rd parameter Culture is actaully used to specify the locale and is optional. You can use different Cultures to modify your datetimes in different languages and formats as shown above.

Check this Video tutorial on CONCAT() function: