Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SQL Server, T-SQL, BI and more...
Browse by Tags · View All
ssis 26
SSIS 26
SQLServerPedia Syndication 19
Business Intelligence 14
T-SQL 13
ssrs 10
SSRS 10
General 9
SSAS 8
sqlpass 4

Archive · View All
January 2012 14
April 2012 5
March 2012 5
February 2012 5
November 2012 3
October 2012 3
June 2012 3
May 2012 3
August 2012 2
July 2012 2

Sam Vanga's Blog

New string function in SQL Server 2012 – FORMAT()

Jan 7 2012 12:35AM by Samuel Vanga   

Formatting numbers in an SSRS report is a common task. For example, you may want to format a number as currency or percentage.

You can select a format from the number page of the properties window.

image

You can let sql handle the formatting, so data in the result set is pre-formatted.

DECLARE @Sales MONEY = 32182000.85;

SELECT ‘$’
+ CONVERT(VARCHAR(32),@Sales,1);

Results:

image

Finally, you can use the newly introduced FORMAT() function in SQL Server 2012. Format() will, according to books online, return a value formatted with the specified format and optional culture. So, instead of converting and concatenating like we did in the previous example, FORMAT() can be used:

DECLARE @Sales MONEY = 32182000.85;

SELECT FORMAT(@Sales,‘c’,‘en-us’);

Results:

image

FORMAT() accepts the following parameters:

  • Value. Actual value that needs to be formatted.
  • Format. Value will be formatted to the specified format. Currency, percentage, and date are few examples.
  • Optional Culture. Specifies the language. More about cultures on BOL.PARSE()

Consider the following query. Value is formatted to three different languages based on the culture:

Formatting Currency:

DECLARE @Sales MONEY = 32182000.85;

SELECT FORMAT(@Sales,‘c’,‘it-IT’) [Italy]
, FORMAT(@Sales,‘c’,‘fr’) [France]
, FORMAT(@Sales,‘c’,‘ru-RU’) [Russian];

Results:

image

Formatting percentages:

DECLARE @Per DECIMAL(2,2) = 0.72;

SELECT FORMAT(@Per,‘p0′,‘en-us’)
,
FORMAT(@Per,‘p2′,‘en-us’);

Results:

image

Conclusion:

Similar formatting is ideally done in the presentation layer, reporting services for example. But I would want to let reporting services do minimal processing. FORMAT() simplifies string formatting. It provides functionality that most developers have always wanted.

Aaron Bertrand (blog), MVP, wrote a more detail article on this topic. Don’t forget to check that out.


Republished from Sam Vanga's Blog [16 clicks].  Read the original version here [4 clicks].

Samuel Vanga
549 · 0% · 68
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"New string function in SQL Server 2012 – FORMAT()" rated 5 out of 5 by 1 readers
New string function in SQL Server 2012 – FORMAT() , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]