{"id":5414,"date":"2013-04-08T00:00:17","date_gmt":"2013-04-08T00:00:17","guid":{"rendered":"http:\/\/craftydba.com\/?p=5414"},"modified":"2017-10-11T16:22:16","modified_gmt":"2017-10-11T16:22:16","slug":"string-functions-format","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=5414","title":{"rendered":"String Functions – FORMAT()"},"content":{"rendered":"

\"\"<\/a>
\nI am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the FORMAT() function today.<\/p>\n

The FORMAT()<\/a> 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.<\/p>\n

I personally think this string function was a valuable addition to SQL Server 2012. Please refer to the .NET Framework 4.5 formatting types<\/a> for both standard and custom format strings.<\/p>\n

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.<\/p>\n

\r\n-- Currency format\r\nselect format(10.95, 'C') as str_currency\r\n\r\n-- Number format\r\nselect format(123.456789 , 'N') as str_number\r\n\r\n-- Percentage format\r\nselect format(10.95 \/ 100 , 'P') as str_percent\r\n\r\n-- Hexidecimal format\r\nselect format(255 , 'X4') as str_hex\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n\r\nstr_currency\r\n-------------\r\n$10.95\r\n\r\nstr_number\r\n-----------\r\n123.46\r\n\r\nstr_percent\r\n------------\r\n10.95 %\r\n\r\nstr_hex\r\n--------\r\n00FF\r\n<\/pre>\n<\/p>\n
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.<\/p>\n
The following examples convert a date\/time variable to different formatted strings.<\/p>\n
\r\n-- Date \/ time variable\r\ndeclare @var_dt datetime2 = '6\/15\/2009 1:45:30 PM';\r\n\r\n-- Short date english\r\nselect format(@var_dt, 'd', 'en-us') as str_english_date\r\n\r\n-- Short date japan\r\nselect format(@var_dt, 'd', 'ja-jp') as str_japanse_date\r\n\r\n\r\n-- Short time english\r\nselect format(@var_dt, 't', 'en-us') as str_english_time\r\n\r\n-- Short time japan\r\nselect format(@var_dt, 't', 'ja-jp') as str_japanese_time\r\n\r\n\r\n-- Month name english\r\nselect format(@var_dt, 'MMM', 'en-us') as str_english_month\r\n\r\n-- Month name france\r\nselect format(@var_dt, 'MMM', 'fr-FR') as str_french_month\r\n\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n\r\nstr_english_date\r\n-----------------\r\n6\/15\/2009\r\n\r\nstr_japanse_date\r\n-----------------\r\n2009\/06\/15\r\n\r\nstr_english_time\r\n-----------------\r\n1:45 PM\r\n\r\nstr_japanese_time\r\n------------------\r\n13:45\r\n\r\nstr_english_month\r\n------------------\r\nJun\r\n\r\nstr_french_month\r\n-----------------\r\njuin\r\n\r\n<\/pre>\n<\/p>\n
One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values?  <\/p>\n
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.<\/p>\n
\r\n-- Null is invalid\r\nselect format(NULL, 'N') as str_number\r\n\r\n-- Null is invalid\r\nselect format(123, NULL) as str_number\r\n\r\n-- Null is invalid\r\nselect format(cast('6\/15\/2009' as date), 'MMM', NULL) as str_month\r\n\r\n\r\n-- Empty is invalid\r\nselect format('', 'N') as str_number\r\n\r\n-- Empty format string ignored\r\nselect format(123, '') as str_number\r\n\r\n-- Empty is invalid\r\nselect format(cast('6\/15\/2009' as date), 'MMM', '') as str_month\r\n\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n\r\nMsg 8116, Level 16, State 1, Line 2\r\nArgument data type NULL is invalid for argument 1 of format function.\r\n\r\nMsg 8116, Level 16, State 1, Line 2\r\nArgument data type NULL is invalid for argument 2 of format function.\r\n\r\nMsg 8116, Level 16, State 1, Line 2\r\nArgument data type NULL is invalid for argument 3 of format function.\r\n\r\n\r\nMsg 8116, Level 16, State 1, Line 2\r\nArgument data type varchar is invalid for argument 1 of format function.\r\n\r\nstr_number\r\n----------\r\n123\r\n\r\nstr_month\r\n----------\r\nMsg 9818, Level 16, State 1, Line 2\r\nThe culture parameter '' provided in the function call is not supported.\r\n\r\n<\/pre>\n<\/p>\n
In summary, the FORMAT() function is a great addition to the TSQL language.  I will be blogging about the LEFT()<\/a> function next time.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,362,15,815,29],"class_list":["post-5414","post","type-post","status-publish","format-standard","hentry","category-very-short-articles","tag-database-developer","tag-format","tag-john-f-miner-iii","tag-string-function","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5414","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5414"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5414\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}