Introduction
SQL Server 2012 introduced two new string functions: CONCAT and FORMAT.
CONCAT
The CONCAT string function concatenates two or more strings into one string. CONCAT takes string arguments as input and concatenates these string inputs into a single string. It requires a minimum of two strings as input, otherwise it raises a compile time error. Here all arguments (inputs) are converted into a string type implicitly. A null value is implicitly converted into an empty string.
Syntax
CONCAT ( stringvalue1, stringvalue2 ,…, stringvalueN )
Argument / parameter
String Value: A String value to concatenate with the other.
Example
--Example of simple string concatenation
SELECT CONCAT('Hi, ', 'I AM ', 'Jignesh Trivedi' )
--Output
-- Hi, I AM Jignesh Trivedi
--Example of NULL string concatenation
SELECT CONCAT('Hello ', NULL, 'World' );
--Output
-- Hello World
--Example of other Data type concatenation with string
SELECT CONCAT('Date : ', CAST(GETDATE() AS DATE));
--Date : 2013-09-25
SELECT CONCAT(123 + '.' + 45 );
--123.45
If all arguments are null then this function returns a string of type VARCAHR (1). The return type of this function depends on the arguments.
- If an argument is a SQL type NVARCHAR (MAX) or SQL CLR system type then the return type of this function is NVARCHAR (MAX)
- If an argument is VARBINARY (MAX) or VARCHAR (MAX) then the result type is VARCHAR (MAX) and if one of argument is NVARCHAR then the output is NVARCHAR (MAX).
- If an argument is NVARCHAR (<= 4000) than result type is NVARCHAR (<= 4000)
- All other cases result in a type of VARCHAR (<=8000).
- When the length of the arguments are less than 4000 for NVARCHAR or less than 8000 for VARCHAR, implicit conversions can affect the length of the result type.
Other data types, like INT and FLOAT have different lengths when converted to a string. For example, an INT data type length is 12 when converted to a string, so the result of concatenating two integers has a length of 24.
FORMAT
The FORMAT string function returns a string formatted value with the specified format and culture (this is optional). We can use the FORMAT function for locale-aware formatting of a date and time and number values as a string.
Syntax
FORMAT (Value, Format , Culture)
Arguments / parameters
Value - value in supported data type to format
Format - NVARCHAR format pattern. This argument must contain a valid .NET framework format string. Composite formatting is not supported. It is either a standard format string or a pattern for custom characters for dates and numeric values.
Culture - NVARCHAR Type specifies a culture.
A culture argument is optional, if we are not providing the culture value then the language of current session is used. The language can be set implicitly or explicitly (using a SET LANGUAGE statement). The culture argument accepts all cultures supported by the .NET Framework. It is not limited to the languages supported by SQL Server. If the culture argument is invalid then the Format string function throws an error.
Please refer to http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo(v=vs.80).aspx to learn more about cultures supported by the .NET Framework.
Return Type is NVARCHAR or NULL
A FORMAT string function always returns NULL for an error when the culture is invalid. For the argument value, supported data types are numeric (like TINYINT, SMALLINT, INT, BIGINT, FLOAT, NUMERIC, DECIMAL, SMALLMONEY, MONEY and REAL) and date and time data type (like DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2 and DATETIMEOFFSET). This function will not be remote and it depends on the presence of the Common Language Runtime (CLR).
Example
--Example of simple FORMAT string function
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'd', 'en-US' ) AS 'US Format'
,FORMAT ( @mydate, 'd', 'en-gb' ) AS 'GB Format'
--Output
--US Format GB Format
---------------- -------------
--09/25/2013 25/09/2013
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'D', 'en-US' ) AS 'US Format'
,FORMAT ( @mydate, 'D', 'en-gb' ) AS 'GB Format'
--Output
--US Format GB Format
--Wednesday,September 25, 2013 25 September 2013
--Example of custom formatting string
DECLARE @mydate DATETIME = '09/25/2013';
SELECT FORMAT ( @mydate, 'dd/MM/yyyy', 'en-US' )
--Output
--25/09/2013
SELECT FORMAT(555230655,'###-##-####')
--Output
--555-23-0655
--Example of formatting numeric type
SELECT FORMAT (8875.644 , 'C' , 'en-US' ) AS 'Currency Format1'
,FORMAT (8875.644 , 'C0', 'en-US' ) AS 'Currency Format2'
Output
-- Currency Format1 Currency Format2
$8,875.64 $8,876
-- Please refer http://msdn.microsoft.com/library/dwhawy9k.aspx to know more about standard Numeric Format String.
-- Please refer http://msdn.microsoft.com/en-us/library/0c899ak8.aspx to know about custom Numeric Format String.
Conclusion
These two newly introduced functions are very useful. The CONCAT string function is useful to concatenate two or more values and values of one or more data types. The FORMAT string function gets a formatted value with the specified format and optional culture.