FORMATNUM
Updated: 30 April 2009
Use FORMATNUM to convert a numeric value to text and specify the display formatting by using special format strings.
Syntax
SELECT [wctString].[wct].[FORMATNUM] (
<@Value, float,>
,<@Format, nvarchar(4000),>)
Arguments
@Value
is the value to be formatted. @Value is an expression of type float or of a type that can be implicitly converted to float.
@Format
A numeric format as a text string, for example '#,#.00'. The @Format argument can be of data types that are implicitly convertible to nvarchar or ntext.
Return Types
nvarchar(4000)
Remarks
· FORMATNUM uses format strings found in the .NET framework. TEXT uses format strings found in EXCEL.
· Use 0 as the zero placeholder. If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the result string; otherwise, a '0' appears in the result string. The position of the leftmost '0' before the decimal point and the rightmost '0' after the decimal point determines the range of digits that are always present in the result string.
The "00" specifier causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "00" would result in the value 35.
· Use # as the digit placeholder. If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string.
Note that this specifier never displays the '0' character if it is not a significant digit, even if '0' is the only digit in the string. It will display the '0' character if it is a significant digit in the number being displayed.
The "##" format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "##" would result in the value 35.
· Use '. ' as the decimal point. The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored.
The actual character used as the decimal separator in the result string is determined by the NumberDecimalSeparator property of the NumberFormatInfo object that controls formatting.
· The ',' character serves as both a thousand separator specifier and a number scaling specifier.
Thousand separator specifier: If one or more ',' characters is specified between two digit placeholders (0 or #) that format the integral digits of a number, a group separator character is inserted between each number group in the integral part of the output.
The NumberGroupSeparator and NumberGroupSizes properties of the current NumberFormatInfo object determine the character used as the number group separator and the size of each number group. For example, if the string '#,#' and the invariant culture are used to format the number 1000, the output is '1,000'.
Number scaling specifier: If one or more ',' characters is specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 each time a number scaling specifier occurs. For example, if the string '0,, ' is used to format the number 100 million, the output is '100'.
You can use thousand separator and number scaling specifiers in the same format string. For example, if the string '#,0,, ' and the invariant culture are used to format the number one billion, the output is '1,000'.
· The presence of a '%' character in a format string causes a number to be multiplied by 100 before it is formatted. The appropriate symbol is inserted in the number itself at the location where the '%' appears in the format string. The percent character used is dependent on the current NumberFormatInfo class.
· The presence of a '‰' character (Char(137)) in a format string causes a number to be multiplied by 1000 before it is formatted. The appropriate per mille symbol is inserted in the returned string at the location where the '‰' symbol appears in the format string. The per mille character used is defined by the NumberFormatInfo..::.PerMilleSymbol property of the object that provides culture-specific formatting information.
· If any of the strings 'E', 'E+', 'E-', 'e', 'e+', or 'e-' are present in the format string and are followed immediately by at least one '0' character, then the number is formatted using scientific notation with an 'E' or 'e' inserted between the number and the exponent. The number of '0' characters following the scientific notation indicator determines the minimum number of digits to output for the exponent. The 'E+' and 'e+' formats indicate that a sign character (plus or minus) should always precede the exponent. The 'E', 'E-', 'e', or 'e-' formats indicate that a sign character should only precede negative exponents.
· Characters enclosed in single or double quotes are copied to the result string, and do not affect formatting.
· The ';' character is used to separate sections for positive, negative, and zero numbers in the format string. If there are two sections in the custom format string, the leftmost section defines the formatting of positive and zero numbers, while the rightmost section defines the formatting of negative numbers. If there are three sections, the leftmost section defines the formatting of positive numbers, the middle section defines the formatting of negative numbers, and the rightmost section defines the formatting of zero numbers.
· Any other character is copied to the result string, and does not affect formatting.
· Note that for fixed-point format strings (that is, format strings that do not contain scientific notation format characters), numbers are rounded to as many decimal places as there are digit placeholders to the right of the decimal point. If the format string does not contain a decimal point, the number is rounded to the nearest integer. If the number has more digits than there are digit placeholders to the left of the decimal point, the extra digits are copied to the result string immediately before the first digit placeholder.
Examples
CREATE TABLE #n (
[recno] [tinyint] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](35) NOT NULL
)
INSERT INTO #n VALUES( 1, 1234567.890, '#')
INSERT INTO #n VALUES( 2, 1234567.890, '#,#')
INSERT INTO #n VALUES( 3, 1234567.890, '#.##')
INSERT INTO #n VALUES( 4, 1234567.890, '#,#.##')
INSERT INTO #n VALUES( 5, 1234567.890, '#,#.###')
INSERT INTO #n VALUES( 6, 1234567.890, '#,#.0')
INSERT INTO #n VALUES( 7, 1234567.890, '#,#.00')
INSERT INTO #n VALUES( 8, 1234567.890, '#,#.000')
INSERT INTO #n VALUES( 9, 123.890, '##')
INSERT INTO #n VALUES( 10, 123.890, '##.#')
INSERT INTO #n VALUES( 12, 1E6, '$#,#.00')
INSERT INTO #n VALUES( 13, 1E6, '€#,#.00')
INSERT INTO #n VALUES( 14, 1E6, '£#,#.00')
INSERT INTO #n VALUES( 15, 1E6, '¥#,#.##')
INSERT INTO #n VALUES( 16, exp(1), '#.00000 00000 00000')
INSERT INTO #n VALUES( 17, 1e-15, '#.00000 00000 00000')
INSERT INTO #n VALUES( 18, 299792458, '#.0E0 "m/s"')
INSERT INTO #n VALUES( 19, 299792458, '#.00e0 "m/s"')
INSERT INTO #n VALUES( 20, 299792458, '#.000E+0 "m/s"')
INSERT INTO #n VALUES( 21, 10000000000, '#,#,,, "billion";(#,#),,, "billion"')
INSERT INTO #n VALUES( 22, -10000000000, '#,#,,, "billion";(#,#),,, "billion"')
select *, wct.FORMATNUM(num_val, Num_format)
from #n
This produces the following result
recno num_val num_format
----- ---------------------- ----------------------------------- ---------------
1 1234567.89 # 1234568
2 1234567.89 #,# 1,234,568
3 1234567.89 #.## 1234567.89
4 1234567.89 #,#.## 1,234,567.89
5 1234567.89 #,#.### 1,234,567.89
6 1234567.89 #,#.0 1,234,567.9
7 1234567.89 #,#.00 1,234,567.89
8 1234567.89 #,#.000 1,234,567.890
9 123.89 ## 124
10 123.89 ##.# 123.9
12 1000000 $#,#.00 $1,000,000.00
13 1000000 €#,#.00 €1,000,000.00
14 1000000 £#,#.00 £1,000,000.00
15 1000000 ¥#,#.## ¥1,000,000
16 2.71828182845905 #.00000 00000 00000 2.71828 18284 59050
17 1E-15 #.00000 00000 00000 .00000 00000 00001
18 299792458 #.0E0 "m/s" 3.0E8 m/s
19 299792458 #.00e0 "m/s" 3.00e8 m/s
20 299792458 #.000E+0 "m/s" 2.998E+8 m/s
21 10000000000 #,#,,, "billion";(#,#),,, "billion" 10 billion
22 -10000000000 #,#,,, "billion";(#,#),,, "billion" (10) billion
(21 row(s) affected)