I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the FORMAT() function today.
The FORMAT() function takes a value [V], a format string [F], and a optional culture specification [C] as input parameters. It returns a locale-aware formatted string. For general conversions, consider using the CAST or CONVERT functions.
I personally think this string function was a valuable addition to SQL Server 2012. Please refer to the .NET Framework 4.5 formatting types for both standard and custom format strings.
The following examples convert numbers in different data types to formatted strings. Before the FORMAT() function, a database developer would have to create a user defined function for these algorithms.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Currency format select format(10.95, 'C') as str_currency -- Number format select format(123.456789 , 'N') as str_number -- Percentage format select format(10.95 / 100 , 'P') as str_percent -- Hexidecimal format select format(255 , 'X4') as str_hex |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
output: str_currency ------------- $10.95 str_number ----------- 123.46 str_percent ------------ 10.95 % str_hex -------- 00FF |
The real power of the FORMAT() function can easily be shown with date and time data types. We can use different culture specifications to achieve different results.
The following examples convert a date/time variable to different formatted strings.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- Date / time variable declare @var_dt datetime2 = '6/15/2009 1:45:30 PM'; -- Short date english select format(@var_dt, 'd', 'en-us') as str_english_date -- Short date japan select format(@var_dt, 'd', 'ja-jp') as str_japanse_date -- Short time english select format(@var_dt, 't', 'en-us') as str_english_time -- Short time japan select format(@var_dt, 't', 'ja-jp') as str_japanese_time -- Month name english select format(@var_dt, 'MMM', 'en-us') as str_english_month -- Month name france select format(@var_dt, 'MMM', 'fr-FR') as str_french_month |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
output: str_english_date ----------------- 6/15/2009 str_japanse_date ----------------- 2009/06/15 str_english_time ----------------- 1:45 PM str_japanese_time ------------------ 13:45 str_english_month ------------------ Jun str_french_month ----------------- juin |
One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values?
A NULL value results in errors for all three parameters, a empty string is ignored as a format string and a empty string for the other two parameters results in errors.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Null is invalid select format(NULL, 'N') as str_number -- Null is invalid select format(123, NULL) as str_number -- Null is invalid select format(cast('6/15/2009' as date), 'MMM', NULL) as str_month -- Empty is invalid select format('', 'N') as str_number -- Empty format string ignored select format(123, '') as str_number -- Empty is invalid select format(cast('6/15/2009' as date), 'MMM', '') as str_month |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
output: Msg 8116, Level 16, State 1, Line 2 Argument data type NULL is invalid for argument 1 of format function. Msg 8116, Level 16, State 1, Line 2 Argument data type NULL is invalid for argument 2 of format function. Msg 8116, Level 16, State 1, Line 2 Argument data type NULL is invalid for argument 3 of format function. Msg 8116, Level 16, State 1, Line 2 Argument data type varchar is invalid for argument 1 of format function. str_number ---------- 123 str_month ---------- Msg 9818, Level 16, State 1, Line 2 The culture parameter '' provided in the function call is not supported. |
In summary, the FORMAT() function is a great addition to the TSQL language. I will be blogging about the LEFT() function next time.
