FormatNumeric

Table of Contents



Formats a number using a .NET numeric format string and returns the result as text.

String FormatNumeric(Numeric value, String format = 'N')

Format Description Example (1234.567)
C Currency $1,234.57
E Exponential (scientific) 1.23e+003 (E2)
F Fixed-point 1234.57 (F2)
G General 1234.567
N Number with thousands separators 1,234.57
P Percent 25.6% for 0.256 (P1)


Character Description Example (1234.567)
0 Zero placeholder 00000.0 → 01234.6
# Digit placeholder #.## → 1234.57
. Decimal point 0.00 → 1234.57
, Thousands separator #,{Function (0) does not have a defined descriptor.} → 1,235
% Percentage 0% → 123457%

Integer-only formats

Numbers are evaluated as decimals, so the integer-only formats D and X are rejected with "Format specifier was invalid".


round-pushpin Formats a value in several ways.
SELECT
    FormatNumeric(1234.567) as Default,
    FormatNumeric(1234.567, 'C') as Currency,
    FormatNumeric(1234.567, 'F2') as Fixed,
    FormatNumeric(1234.567, 'N1') as Number,
    FormatNumeric(0.256, 'P1') as Percent,
    FormatNumeric(1234.567, '#,##0.0') as Custom
FROM
    Single




SQL :: IsNotLike
Returns true (1) if the text does not match the pattern. See Like for the wildcards.
SQL :: IsNull
Returns true (1) if the given value is null, otherwise false (0). This is how to test for missing fields and for the unmatched side of an outer join.
SQL :: IsNumeric
Returns true (1) if the given value is a number: digits with an optional leading sign and an optional decimal point. Null and empty values return 0.
SQL :: IsString
Returns true (1) if the given value cannot be converted to a number.
SQL :: LastIndexOf
Returns the zero-based position of the last occurrence of textToFind in textToSearch. Returns -1 when it is not found.
SQL :: Left
Returns the given number of characters from the start of the value.
SQL :: Length
Returns the number of characters in the given value.
SQL :: NullIf
Returns null when the conditional is true, otherwise returns the value.
SQL :: NullIfNumeric
Returns null when the conditional is true, otherwise returns the number.
SQL :: NullWhen
Returns null when the value equals compareToValue, otherwise returns the value.