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.
- 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.
- A
subpattern
that defines positive numbers. - An optional
subpattern
that defines negative numbers. (If only onesubpattern
is defined, negative numbers use the positive pattern, prefixed with a minus sign.) - The optional parameters
groupsep
anddecsep
.
Syntax
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).
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
- An optional prefix (chars)
- A mandatory pattern representing a whole number
- An optional pattern representing decimal places
- An optional pattern representing an exponent (the power by which the preceding number is raised)
- An optional suffix (chars)
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
.
Examples of formatting patterns
The following table shows formatting patterns and the strings that are generated from sample numeric input.
Pattern | Input number | Output string |
---|---|---|
+###,##0.00;-###,###,##0.00:groupsep='':decsep=, | 123456789.123 | +123'456'789,12 |
##0.00 | 1000000 | 1000000.00 |
##0.00 | 3.14159265 | 3.14 |