|
|
SQL Server 2012 introduces 2 new string functions, namely the CONCAT string function and the FORMAT string function. The CONCAT string function concatenates two or more string values while the FORMAT string function formats a value with the specified format and optional culture.
CONCAT String Function
The CONCAT string function, which is short for concatenate, returns a string that is the result of concatenating two or more string values.
|
CONCAT ( <string_value1>, <string_value2> [, <string_valueN> ] )
The input parameters, string_value1, string_value2, string_valueN, are the string values that will be concatenated together in the order specified. All arguments are implicitly converted to string types and then concatenated. NULL values are implicitly converted to an empty string. If all arguments are NULL, an empty string of type VARCHAR(1) is returned. The implicit conversion of strings follows the existing rules for data type conversions.
Prior to SQL Server 2012, to concatenate strings the string concatenation operator used is the plus (+) sign. This string concatenation operator is still valid in SQL Server 2012. There are 2 notable differences between these 2 ways of concatenating strings.
The first difference is in the handling NULL values. Using the + string concatenation operator, if any of the string values being concatenated is NULL and the CONCAT_NULL_YIELDS_NULL setting is set to ON, then the result will be NULL. With the CONCAT string function, regardless of the setting of the CONCAT_NULL_YIELDS_NULL option, all NULL values are implicitly converted to an empty string.
Another notable difference is in concatenating values of different data types. With the CONCAT string function, all non-string data types are implicitly converted to string data type before being concatenated. With the + string concatenation operator, if the values being concatenated are of different data types, the rules of data precedence will convert the data type of the lower precedence to the data type with the higher precedence. If the conversion is not a supported implicit conversion then an error is generated.
FORMAT String Function
The FORMAT string function returns a value formatted with the specified format and optional culture.
FORMAT ( <value>, <format> [, <culture> ] )
The first argument, <value>, is the expression to be formatted. It has to be one of the supported data types, which are either numeric data types (BIGINT, INT, SMALLINT, TINYINT, DECIMAL, NUMERIC, FLOAT, REAL, SMALLMONEY or MONEY) or date and time data types (DATE, TIME, DATETIME, SMALLDATETIME, DATETIME2 or DATETIMEOFFSET).
The second argument, <format>, must contain a valid .NET Framework format string, either as a standard format string, such as 'd' (short date pattern) or ‘F’ (full date/time pattern) for date/time data type format or ‘C’ (currency), ‘N’ (number) or ‘P’ (percent) for numeric data type format, or as a pattern of custom characters for dates and numeric values (for example, 'MM/dd/yyyy hh:mm:ss tt').
Here’s a list of commonly used formats both for date/time data types and numeric data types:
| Standard Date and Time Format Strings |
| 'd' |
Short date pattern. |
FORMAT(SYSDATETIME(),'d') |
1/24/2012 |
| 'D' |
Long date pattern. |
FORMAT(SYSDATETIME(),'D') |
Tuesday, January 24, 2012 |
| 'f' |
Full date/time pattern (short time). |
FORMAT(SYSDATETIME(),'f') |
Tuesday, January 24, 2012 2:19 PM |
| 'F' |
Full date/time pattern (long time). |
FORMAT(SYSDATETIME(),'F') |
Tuesday, January 24, 2012 2:19:39 PM |
| 'g' |
General date/time pattern (short time). |
FORMAT(SYSDATETIME(),'g') |
1/24/2012 2:19 PM |
| 'G' |
General date/time pattern (long time). |
FORMAT(SYSDATETIME(),'G') |
1/24/2012 2:19:39 PM |
| 't' |
Short time pattern. |
FORMAT(SYSDATETIME(),'t') |
2:19 PM |
| 'T' |
Long time pattern. |
FORMAT(SYSDATETIME(),'T') |
2:19:39 PM |
| Standard Numeric Format Strings |
| 'C' or 'c' |
Currency |
FORMAT(9876.543,'C')
FORMAT(9876.543,'C0') |
$9,876.54
$9,877 |
| 'D' or 'd' |
Decimal |
FORMAT(9876543,'D')
FORMAT(9876543,'D10') |
9876543
0009876543 |
| 'N' or 'n' |
Numeric |
FORMAT(9876.543,'N')
FORMAT(9876.543,'N0') |
9,876.54
9,877 |
| 'P' or 'p' |
Percent |
FORMAT(0.98765,'P')
FORMAT(0.98765,'P1') |
98.77%
98.8% |
The third argument, <culture>, is optional and is used to specify the culture, which is any culture supported by the .NET Framework. If this argument is not provided, then the language of the current session is used.
Here are a few cultures that can be specified on this optional argument:
| Culture Name |
Language - Country/Region |
| en-US |
English - United States |
| en-GB |
English - United Kingdom |
| fr-FR |
French - France |
| it-IT |
Italian - Italy |
| nl-NL |
Dutch - The Netherlands |
| de-DE |
German - Germany |
| pt-BR |
Portuguese - Brazil |
| pt-PT |
Portuguese - Portugal |
| es |
Spanish |
| es-AR |
Spanish - Argentina |
|