SQL Server 2012 (a.k.a Denali) – New Functions: CONCAT & FORMAT

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.

–> 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.