New String Functions in SQL Server 2012

String Functions

FORMAT Function
The format function is used to format how a field is to be displayed.

Syntax:
Format(Column_name,format)
Format converts the first argument to a specified format and returns the string value.

Example:
Declare @d DateTime=’20/03/2011’
Select Format(@d,’d’,’en-US’) as US_Result

Output:
This function formats the datetime. this function is used in the server .NET Framework and CLR. The function will solve many formatting issues for developers.

CONCAT Function
It’s the same concatenate function that we use in Excel; it will concatenate two or more strings to make it single string. It implicitly converts all arguments to string types.

Syntax:
CONCAT(string_value1,string_value2,- - - - ,[string_valueN])
String_value: A String value to concatenate to the other values.

Example:
Select concat(‘rock’,’-’,’smith’) as Concatestring

Output:

This function expects at least two parameters and a maximum of 254 parameters.