Formatting and parsing numbers as strings

For conversions between string data types and numeric data types, you can supply, on the FORMAT parameter of the CAST function, an optional formatting expression.

For conversions from string types, the formatting expression defines how the source string should be parsed to fill the target data type.

For conversions to string types, the formatting expression defines how the data in the source expression is to be formatted in the target string.

You can specify a FORMAT expression for the following numeric conversions (Specifying a FORMAT expression for date/time conversions is described in Formatting and parsing dateTimes as strings).
  • From any of the string data types (BIT, BLOB, or CHARACTER) to:
    • DECIMAL
    • FLOAT
    • INTEGER
  • To any of the string data types (BIT, BLOB, or CHARACTER) from any of the numeric data types that are in the previous list.
The formatting expression consists of three parts:
  1. A subpattern that defines positive numbers.
  2. An optional subpattern that defines negative numbers. (If only one subpattern is defined, negative numbers use the positive pattern, prefixed with a minus sign.)
  3. The optional parameters groupsep and decsep.

Syntax

Read syntax diagramSkip visual syntax diagramsubpattern;subpattern:groupsep=chars:decsep=chars
subpattern
Read syntax diagramSkip visual syntax diagramcharsdigits.digitseEdigitschars

Parameters

chars

A sequence of zero or more characters. All characters can be used, except the special characters that are listed under subpattern.

decsep

One or more characters to be used as the separator between the whole and decimal parts of a number (the decimal separator). The default decimal separator is a period (.).

digits

A sequence of one or more of the numeric tokens (0 # - + , . ) that are listed under subpattern.

groupsep

One or more characters to be used as the separator between clusters of integers, to make large numbers more readable (the grouping separator). The default grouping separator is nothing (that is, there is no grouping of digits or separation of groups).

Grouping is commonly done in thousands, but it can be redefined by either the pattern or the locale. There are two grouping sizes:
The primary grouping size
Used for the least significant integer digits.
The secondary grouping size
Used for all other integer digits.
In most cases, the primary and secondary grouping sizes are the same, but they can be different. For example, if the pattern used is #,##,##0, the primary grouping size is 3 and the secondary is 2. The number 123456789 would become the string 12,34,56,789.

If multiple grouping separators are used (as in the previous example), the rightmost separator defines the primary size, and the penultimate rightmost separator defines the secondary size.

subpattern

The subpattern consists of:
  1. An optional prefix (chars)
  2. A mandatory pattern representing a whole number
  3. An optional pattern representing decimal places
  4. An optional pattern representing an exponent (the power by which the preceding number is raised)
  5. An optional suffix (chars)
Parts 2, 3, and 4 of the subpattern are defined by the tokens in the following table.
Token Represents
0 Any digit, including a leading zero.
# Any digit, excluding a leading zero. (See the explanation of the difference between 0 and # that follows this table.)
. Decimal separator.
+ Prefix of positive numbers.
- Prefix of negative numbers.
, Grouping separator.
E/e Separates the number from the exponent.
; Subpattern boundary.
' Quotation mark, applied to special characters. If a quotation mark is needed in output, it must be doubled ('').
* Padding specifier. The character following the asterisk is used to pad the number to fit the length of the format pattern.

The # and 0 characters are used for digit substitution, the difference between them being that a # character is removed if there is no number to replace it with. For example, 10 formatted by the pattern #,##0.00 gives 10.00, but formatted by 0,000.00 gives 0,010.00.

To specify padding characters, use an asterisk. When an asterisk is placed in either of the two chars regions (the prefix and suffix), the character immediately following it is used to pad the output. Padding can be specified only once. For example, a pattern of *x#,###,##0.00 applied to 1234 gives xxx1,234.00. But applied to 1234567, it gives 1,234,567.00.