SQL format models¶

Format elements¶

Format elements are sequences of digits and/or letters (mostly case-insensitive), and, in some cases, symbols. Format elements can be directly concatenated to each other.

Some format elements are used commonly across all format models for controlling printing and matching input text. Other format elements have specific uses based on the type of values they are used to cast to/from. For more information, see the following sections in this topic:

• Format Modifiers and Generic Space Handling

• Fixed-position Format Elements

• Text-minimal Format Elements

Format literals¶

Format literals are sequences that can consist of combinations of:

• Strings of arbitrary characters delimited by double quotes (a double quote is represented as two adjacent double quotes).

• One or more of the following symbols:

  Symbol/Character	Notes
  . (period)	In fixed numeric models, treated as a format element when following 0, 9, or X; otherwise preserved as-is.
  , (comma)	In numeric models, treated as a format element when following 0, 9, or X; otherwise preserved as-is.
  ; (semi-colon)	Always preserved as-is.
  : (colon)	Always preserved as-is.
  - (minus sign)	Always preserved as-is.
  = (equal sign)	Always preserved as-is.
  / (forward slash)	Always preserved as-is.
  ( (left parenthesis)	Always preserved as-is.
  ) (right parenthesis)	Always preserved as-is.

A literal is always printed as-is, exactly where it was located in the format model.

Here is a brief example of using a SQL format model to print the minus sign after a number rather than before it. The MI indicates where to put the minus sign if the number is a negative number.

select to_varchar(-123.45, '999.99MI') as EXAMPLE;

Copy

The output would look similar to 123.45- rather than the default -123.45.

More examples are included at the end of this topic.

Printing output strings using the fill mode modifier¶

By default, the fill mode is set to fill and the FM fill mode modifier toggles it to compact; repeated use toggles it back to fill, etc.

In most cases, using fill mode on printing guarantees that format elements produce output of a fixed width by padding numbers on the left with leading zeros or spaces, and padding text with spaces on the right. This guarantees that columnar output in fixed-width fonts will be aligned.

In compact mode, most format elements produce only minimum-width output (i.e. leading zeros and spaces and trailing spaces are suppressed).

The format elements that don’t adhere to these rules are explicitly noted below.

The exact match modifier, FX does not affect printing; the underscore format element prints nothing.

Parsing input strings using the modifiers¶

Parsing of input strings is affected by both the fill mode modifier, FM, and the exact match modifier FX. Initially:

• Fill mode is set to fill and FM toggles it to compact and back.

• Exact match mode is set to lax and FX toggles it to exact and back.

All string matching against format elements and literals during parsing is case-insensitive.

In lax mode, the first step of input parsing is skipping leading white space (a sequence of spaces, tabs, LF, CR, FF, and VT characters); the mode at the beginning input is strict if the first format element is FX, and lax otherwise.

In the lax match mode, spaces within literals are matched against any non-empty input sequence of spaces; non-space characters are matched one-to-one. In the exact mode, all characters in a literal must match the input characters one-to-one.

The numeric format elements are matched against the corresponding digit sequences:

• If both fill and exact modes are in effect, the number of digits must exactly correspond to the width of the corresponding numeric format elements (leading zeros are expected).

• If compact or lax mode is in effect, a matching input number must have, at most, the number of digits equal to the maximal width of the format element, and at least one digit; leading zeros are ignored.

The textual format elements are matched case-insensitively:

• If both fill and exact modes are in effect, the number of trailing spaces, up to the max width of the element, is expected.

• Otherwise, spaces after the variable-length textual elements are ignored in lax mode, and exact match to the actual word (without padding spaces) is expected in exact mode.

Finally, the trailing white space until the end of the input string is ignored if the current mode is lax.

Normally, both lax and exact modes do not allow matching spaces where spaces are not present in the format model or could not be generated by printing the content of format elements in fill mode.

Places where spaces should be ignored if present in both lax and exact modes can be explicitly marked using the _ (underscore) format element.

As a rule of thumb, a format in exact mode recognizes only input strings printed by the same format, while a format in lax mode recognizes input strings which were printed by the similar format with any fill mode modifiers added or removed.

Fixed-position numeric formats¶

Fixed-position numbers are represented using digit elements, 0 or 9. For example, 999 holds numbers from 1 to 3 decimal digits. The fractional part of the numbers is delimited using separator elements, . (period) or D:

• . is always rendered as a period.

• To use a different character for the D elements, modify the input string to replace all periods with commas and all commas with periods before applying the cast function.

Normally, the leading zeros in the integer part and trailing zeros in the fractional part are replaced with spaces (except when the value of the integer part is zero, in which case it is rendered as a single 0 character). To suppress this behavior use the 0 format element in place of 9; the corresponding positions have 0 characters preserved. The format element B, when used before the number, suppresses preserving the last 0 in the integer value (i.e. if you use B and the value of the integer part of the number is zero, all digits are rendered as spaces).

The digit group separator , (comma) or G results in the corresponding group separator character being printed if the number is big enough so the digits are on the both sides of group separator. An example of a format model useful for printing currency sums would be 999,999.00.

When there are more digits in the integer part of the number than there are digit positions in the format, all digits are printed as # to indicate overflow.

The exponent element causes fixed-position numbers to be normalized so that the first digit in the integer part is 1 to 9 (unless the value of the number is zero, in which case the value of the exponent is also zero). The EE element automatically picks the right number of digits in the exponent, and does not print the + sign, while EEE, EEEE, and EEEEE always print the + or - sign for the exponent and the requested number of digits (leading zeros are not suppressed). Exponent overflow is indicated by # in place of digits.

The exponent indicators print either capital E or lowercase e depending on the case of the first letter in the format element.

The X format element works like 9, except that hexadecimal digits 0-9A-F are printed. Currently, hexadecimal fractions are not supported. Similar to 9, X replaces leading zeros with spaces. The 0 element, when used together with X prints hexadecimal digits without leading zero suppression (thus use 000X to print hex numbers that always contain 4 digits).

Note that X prints hexadecimal digits with uppercase Latin letters, and lowercase x prints lowercase Latin letters. The hexadecimal 0 format element uses the case of the subsequent X format element.

Normally, hexadecimal numbers are printed as unsigned, i.e. negative numbers have all 1’s in the most significant bit(s), but using the X element together with an explicit sign (S or MI) causes the - sign to be printed along with the absolute value of the number.

Fixed-position numeric format models report overflow on special values (infinity or not-a-number) of floating point numbers.

Text-minimal numeric formats¶

While fixed-position numeric format models always explicitly specify the number of digits, the text-minimal format elements use a minimal number of digits based on the value of the number. The TM* format elements always produce variable-length output with no spaces, regardless of the fill mode modifier (fill or compact).

• TM9 prints the number as an integer or decimal fraction, based on the value of the number. Any decimal fixed-point number value is printed precisely with the number of digits in the fractional part determined by the scale of the number (trailing zeros are preserved in fill mode).

• For floating-point numbers, TM9 picks the number of fractional digits based on the number’s exponent (note that precise binary to decimal fraction conversion is not possible). If the floating-point number’s magnitude is too large, causing the positional notation to be too long, it switches to scientific notation (see TME below). If the floating-point number is too small, TM9 prints zero.

• TME prints the number in scientific notation, i.e. with exponent (same as EE) and one digit in the integer position of the fractional part. The case of the exponent indicator (E or e) matches the case of the first letter (T or t) in the format element.

• TM chooses either TM9 or TME depending on the magnitude of the number, to minimize the length of the text while preserving precision.

Default formats for printing¶

The following table lists the default formats for printing:

Default formats for parsing¶

The following table lists the default formats for parsing:

The list of formats used for automatic optimistic string conversion (i.e. for strings which are automatically recognized as numeric) is the union of all the formats in the above table of default input formats.

Output examples¶

This example shows how to display numbers with leading zeros:

create table sample_numbers (f float);
insert into sample_numbers (f) values (1.2);
insert into sample_numbers (f) values (123.456);
insert into sample_numbers (f) values (1234.56);
insert into sample_numbers (f) values (-123456.789);
select to_varchar(f, '999,999.999'), to_varchar(f, 'S000,000.000') from sample_numbers;

Copy

The output will look similar to:

+------------------------------+-------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'S000,000.000') |
+==============================+===============================+
|        1.2                   | +000,001.200                  |
+------------------------------+-------------------------------+
|      123.456                 | +000,123.456                  |
+------------------------------+-------------------------------+
|    1,234.56                  | +001,234.560                  |
+------------------------------+-------------------------------+
| -123,456.789                 | -123,456.789                  |
+------------------------------+-------------------------------+

Copy

You don’t need leading zeros in order to align numbers. The default fill mode is “fill”, which means that leading blanks are used to align numbers based on the positions of the decimal points.

select to_varchar(f, '999,999.999'), to_varchar(f, 'S999,999.999') from sample_numbers;

Copy

The output will look similar to:

+------------------------------+-------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'S999,999.999') |
+==============================+===============================+
|        1.2                   |       +1.2                    |
+------------------------------+-------------------------------+
|      123.456                 |     +123.456                  |
+------------------------------+-------------------------------+
|    1,234.56                  |   +1,234.56                   |
+------------------------------+-------------------------------+
| -123,456.789                 | -123,456.789                  |
+------------------------------+-------------------------------+

Copy

This example shows what happens if you use the FM (Fill Mode) modifier to switch from “fill” mode to “compact” mode, i.e. to remove leading characters that would align the numbers:

select  to_varchar(f, '999,999.999'), to_varchar(f, 'FM999,999.999') from sample_numbers;

Copy

The output will look similar to:

+------------------------------+--------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'FM999,999.999') |
+==============================+================================+
|        1.2                   | 1.2                            |
+------------------------------+--------------------------------+
|      123.456                 | 123.456                        |
+------------------------------+--------------------------------+
|    1,234.56                  | 1,234.56                       |
+------------------------------+--------------------------------+
| -123,456.789                 | -123,456.789                   |
+------------------------------+--------------------------------+

Copy

This example shows how to display numbers in exponential notation:

select to_char(1234, '9d999EE'), 'will look like', '1.234E3';

Copy

The output will look similar to:

+--------------------------+------------------+-----------+
| TO_CHAR(1234, '9D999EE') | 'WILL LOOK LIKE' | '1.234E3' |
+==========================+==================+===========+
| 1.234E3                  |  will look like  |  1.234E3  |
+--------------------------+------------------+-----------+

Copy

This shows how to include literals in the output. The literal portions are enclosed within double quotes (which, in turn, are inside the single quotes that delimit the string).

select to_char(12, '">"99"<"');

Copy

The output will look similar to:

+-------+
| > 12< |
+-------+

Copy

Input examples¶

These examples demonstrate the use of format models for inputs.

The following example shows some simple input operations, with an emphasis on showing the difference between using “0” and “9” to specify format of digits.

The digit “9” as a formatter will accept blanks or “missing” leading digits. The digit “0” as a formatter will not accept blanks or missing leading zeros.

-- All of the following convert the input to the number 12,345.67.
SELECT TO_NUMBER('012,345.67', '999,999.99', 8, 2);
SELECT TO_NUMBER('12,345.67', '999,999.99', 8, 2);
SELECT TO_NUMBER(' 12,345.67', '999,999.99', 8, 2);
-- The first of the following works, but the others will not convert.
-- (They are not supposed to convert, so "failure" is correct.)
SELECT TO_NUMBER('012,345.67', '000,000.00', 8, 2);
SELECT TO_NUMBER('12,345.67', '000,000.00', 8, 2);
SELECT TO_NUMBER(' 12,345.67', '000,000.00', 8, 2);

Copy

This shows how to accept either of two numeric formats (-### or ###-).

-- Create the table and insert data.
create table format1 (v varchar, i integer);
insert into format1 (v) values ('-101');
insert into format1 (v) values ('102-');
insert into format1 (v) values ('103');

-- Try to convert varchar to integer without a
-- format model.  This fails (as expected)
-- with a message similar to:
--    "Numeric value '102-' is not recognized"
update format1 set i = TO_NUMBER(v);

-- Now try again with a format specifier that allows the minus sign
-- to be at either the beginning or the end of the number.
-- Note the use of the vertical bar ("|") to indicate that
-- either format is acceptable.
update format1 set i = TO_NUMBER(v, 'MI999|999MI');
select i from format1;

Copy