SQL 格式模型¶

格式元素¶

格式元素是数字和/或字母（通常不区分大小写）的序列，有时也包括符号。格式元素之间可以直接连接。

有些格式元素在所有格式模型中都通用，用于控制打印和匹配输入文本。其他格式元素则具有特定用途，具体取决于其用于转换的值类型。有关更多信息，请参阅本主题中的以下部分：

• 格式修饰符和通用空格处理

• 固定位置格式元素

• 最小文本格式元素

格式字面量¶

格式字面量是可以由以下组合组成的序列：

• 由双引号分隔的任意字符串（双引号表示为两个相邻的双引号）。

• 以下一个或多个符号：

  符号/字符	备注
  . （句点）	在固定数字模型中，当跟随 0、9 或 X 时作为格式元素处理；否则保持原样。
  , （逗号）	在数字模型中，当跟随 0、9 或 X 时作为格式元素处理；否则保持原样。
  ; （分号）	始终保持原样。
  : （冒号）	始终保持原样。
  - （减号）	始终保持原样。
  = （等号）	始终保持原样。
  / （正斜杠）	始终保持原样。
  ( （左括号）	始终保持原样。
  ) （右括号）	始终保持原样。

字面量始终按原样打印，位置与格式模型中的位置完全一致。

下面是一个使用 SQL 格式模型在数字之后（而不是数字之前）打印减号的简短示例。 如果数字是负数，则 MI 指明了减号的位置。

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

Copy

输出结果将类似于 123.45- 而不是默认的 -123.45。

本主题末尾包含更多示例。

使用填充模式修饰符打印输出字符串¶

默认情况下，填充模式设置为 填充，FM 填充模式修饰符将其切换为 紧凑；重复使用可将其切换回 填充，依此类推。

大多数情况下，在打印时使用 填充 模式可以保证格式元素生成固定宽度的输出，具体方法是：在数字左侧用前导零或空格进行填充，在文本右侧用空格进行填充。这样可以保证对齐固定宽度字体的列式输出。

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

下面明确指出了不遵守这些规则的格式元素。

精确匹配修饰符 FX 不影响打印；下划线格式元素不打印任何内容。

使用修饰符解析输入字符串¶

输入字符串的解析受填充模式修饰符 FM 和完全匹配修饰符 FX 的影响。初始情况：

• 填充模式设置为 填充，FM 将其切换到 紧凑 再切换回 填充。

• 精确匹配模式设置为 宽松，FX 将其切换到 精确 再切换回 宽松。

解析过程中，所有针对格式元素和字面量的字符串匹配都不区分大小写。

在 宽松 模式下，输入解析的第一步是跳过前导空格（空格、制表符、LF、CR、FF 和 VT 字符的序列）；如果第一个格式元素是 FX，则开始输入时的模式为严格，否则为 宽松。

在 宽松 匹配模式下，字面量中的空格与任意非空的空格输入序列进行匹配；非空格字符一一匹配。在 精确 模式下，字面量中的所有字符必须与输入字符一一匹配。

数字格式元素与相应的数字序列匹配：

• 如果同时使用 填充 模式和 精确 模式，则位数必须与相应数字格式元素的宽度完全对应（可添加前导零）。

• 如果使用 紧凑 模式或 宽松 模式，则匹配的输入数字的位数最多必须等于格式元素的最大宽度，且至少有一位数字；前导零将被忽略。

文本格式元素的匹配不区分大小写：

• 如果同时使用 填充 模式和 精确 模式，则可使用末尾空格，其数量不超过元素的最大宽度。

• 否则，在 宽松 模式下，变量长度文本元素后面的空格将被忽略，而在 精确 模式下应与实际单词完全匹配（不带填充空格）。

最后，如果当前模式为 宽松，则输入字符串末尾的空格将被忽略。

通常情况下，如果格式模型中不存在空格，或者在 填充 模式下打印格式元素的内容时无法生成空格，那么 宽松 模式和 精确 模式都不允许匹配空格。

在 宽松 模式和 精确 模式下，对于出现空格而应忽略空格的位置，可以使用 _ （下划线）格式元素显式标记。

根据经验，精确 模式下的格式仅识别以相同格式打印的输入字符串，而 宽松 模式下的格式则可识别由添加或删除了任何填充模式修饰符的类似格式打印的输入字符串。

固定位置数字格式¶

固定位置数字使用数字元素 0 或 9 表示。例如，999 包含 1 到 3 位十进制数字。数字的小数部分使用分隔符元素 . （句点）或 D 进行分隔：

• . 始终显示为句点。

• 若要对 D 元素使用不同的字符，请在应用 cast 函数之前修改输入字符串，将所有句点替换为逗号，将所有逗号替换为句点。

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 (that is, if you use B and the value of the integer part of the number is zero, all digits are rendered as spaces).

如果数字足够大，则使用数字组分隔符 , （逗号）或 G 会导致打印相应的组分隔符字符，以便数字位于组分隔符的两侧。可用于打印货币总和的格式模型示例是 999,999.00。

当数字整数部分的位数多于格式中的数字位置时，所有位数都打印为 # 以表示溢出。

指数元素会将固定位置的数字标准化，以便整数部分的第一个数字是 1 到 9（除非数字的值为零，在这种情况下，指数的值也为零）。EE 元素会自动选择指数中的正确位数，并且不打印 + 号，而 EEE、EEEE 和 EEEEE 始终打印指数的 + 号或 - 号以及请求的位数（前导零不会被禁止显示）。指数溢出用 # 代替数字表示。

指数指示符根据格式元素中第一个字母的大小写，打印大写 E 或小写 e。

X 格式元素的工作原理与 9 类似，只是打印的是十六进制数字 0-9A-F。目前，不支持十六进制分数。与 9 类似，X 用空格替换前导零。当与 X 一起使用时，0 元素打印的十六进制数不会禁止显示前导零（因此，可使用 000X 打印始终包含 4 位数字的十六进制数字）。

请注意，X 打印十六进制数字时使用大写拉丁字母，而小写 x 打印的是小写拉丁字母。十六进制 0 格式元素使用后续 X 格式元素的大小写。

Normally, hexadecimal numbers are printed as unsigned, that is, 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.

固定位置数字格式模型会报告浮点数的特殊值（无穷大或非数字）的溢出。

最小文本数字格式¶

虽然固定位置数字格式模型始终显式指定位数，但最小文本格式元素根据数字的值使用最少的位数。TM* 格式元素始终生成不带空格的可变长度输出，而不考虑填充模式修饰符（填充 或 紧凑）。

• TM9 根据数字的值将数字打印为整数或小数。任何小数定点数值都会精确打印，小数部分的位数由数字比例决定（在 填充 模式下保留末尾零）。

• 对于浮点数，TM9 根据数字的指数来选择小数位数（请注意，无法将二进制分数精确转换为十进制分数）。如果浮点数的幅度过大，导致以位置记数法表示时过长，则将切换为以科学记数法表示（见下文 TME）。如果浮点数过小，则 TM9 将打印零。

• TME prints the number in scientific notation, that is, 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 根据数字的大小来选择 TM9 或 TME，以便在保持精度的同时尽量减少文本长度。

默认打印格式¶

下表列出了打印的默认格式：

默认解析格式¶

下表列出了解析的默认格式：

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

输出示例¶

此示例演示如何显示带有前导零的数字：

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

输出类似以下内容：

+------------------------------+-------------------------------+
| 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

您不需要前导零来对齐数字。默认的填充模式为“填充”，这意味着系统将根据小数点的位置，使用前导空格来对齐数字。

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

Copy

输出类似以下内容：

+------------------------------+-------------------------------+
| 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, that is, 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

输出类似以下内容：

+------------------------------+--------------------------------+
| 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

此示例演示如何以指数表示法显示数字：

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

Copy

输出类似以下内容：

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

Copy

此示例显示了如何在输出中包含字面量。 字面量部分括在双引号内（而双引号又位于将字符串分隔开来的单引号内）。

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

Copy

输出类似以下内容：

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

Copy

输入示例¶

这些示例演示了对输入使用格式模型。

以下示例演示了一些简单的输入操作，重点显示使用“0”和“9”指定数字格式之间的区别。

作为格式标识符的数字“9”将接受空白或“缺失的”前导数字。作为格式标识符的数字“0”不接受空白或缺失的前导零。

-- 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

此示例显示了如何接受两种数字格式（-### 或 ###-）之一。

-- 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