函数和操作符

1. 概述

UXsinoDB提供了大量的内置数据类型的函数和操作符。本章描述了大部分，尽管其他特殊用途的函数出现在手册的相关部分。用户也可以定义自己的函数和操作符。

本章中用于描述函数或操作符的参数和结果数据类型的符号如下：

repeat (text, integer ) → text

上述示例说明函数 repeat 接受一个文本和一个整数参数，并返回一个文本类型的结果。右箭头也用于指示示例的结果，如下所示：

repeat('Ux', 4) → UxUxUxUx

如果您关心可移植性，那么请注意本章中描述的大多数函数和操作符，除了最普通的算术和比较操作符以及一些显式标记的函数之外，都没有被 SQL 标准指定。其他 SQL 数据库管理系统中也存在这种扩展功能，在许多情况下，这种功能在各种实现之间是兼容和一致的。

2. 逻辑操作符

通常的逻辑操作符如下：

boolean AND boolean → boolean
boolean OR boolean → boolean
NOT boolean → boolean

SQL 使用三值逻辑系统，包括真、假和 null，表示“未知”。请参阅以下真值表：

操作符 AND 和 OR 是可交换的，也就是说，你可以交换左右操作数而不影响结果。（然而，并不能保证左操作数在右操作数之前被评估。）

3. 比较函数和操作符

通常的比较操作符都可用，如表 比较操作符所示。

表 比较操作符

注意
<> 是标准SQL表示“不等于”的符号，而 != 是 UXsinoDB 的扩展。“等于”操作符是 =，不等于操作符是 <>，它是 != 的别名，在解析的早期阶段就被转换为 <>。因此，无法实现执行不同操作的 != 和 <> 操作符。

支持部分多符号操作符中间存在空白（空格或者换行或者制表符，一个或者多个），后续处理中数据库会忽略掉中间的空白。

这些比较操作符适用于所有具有自然排序的内置数据类型，包括数字、字符串和日期/时间类型。此外，如果它们的组件数据类型是可比较的，那么可以比较数组、复合类型和范围。

通常也可以比较相关数据类型的值；例如，integer > bigint 是可行的。这种情况的某些情况是由“跨类型”比较操作符直接实现的，但如果没有这样的操作符，则解析器将强制将较不一般的类型转换为较一般的类型，并应用后者的比较操作符。

支持与 - 或者 + 连用，例如 !=-、!=+、^=-、^=+、>=+ 等。示例如下。

select 1!=-1;
 ?column? 
----------
 t
(1 row)

select 1!=+1;
 ?column? 
----------
 f
(1 row)

select 1^=-1;
 ?column? 
----------
 t
(1 row)

select 1^=+1;
 ?column? 
----------
 f
(1 row)

如上所示，所有比较操作符都是返回 boolean 类型的二元操作符。因此，像 1 < 2 < 3 这样的表达式是无效的（因为没有 < 操作符将布尔值与 3 进行比较）。使用下面显示的 BETWEEN 谓词执行范围测试。

还有一些比较谓词，如表 比较谓词所示。它们的行为很像操作符，但具有SQL标准规定的特殊语法。

表 比较谓词

BETWEEN 谓词简化了范围测试：

a BETWEEN x AND y

等价于

a >= x AND a <= y

请注意，BETWEEN 将端点值视为包含在范围内。BETWEEN SYMMETRIC 与 BETWEEN 类似，除了没有要求左侧的参数小于或等于右侧的参数。如果不是，则自动交换这两个参数，以便始终隐含非空范围。

各种 BETWEEN 变体是基于普通比较操作符实现的，因此适用于任何可比较的数据类型。

注意
BETWEEN 语法中使用 AND 会产生歧义，与逻辑操作符 AND 的使用产生歧义。为了解决这个问题，只允许有限的表达式类型作为 BETWEEN 子句的第二个参数。如果需要在 BETWEEN 中编写更复杂的子表达式，请在子表达式周围加括号。

普通比较操作符在任一输入为 null 时产生 null（表示“未知”），而不是 true 或 false。例如，7 = NULL 产生 null，7 <> NULL 也是如此。当此行为不合适时，请使用 IS [ NOT ] DISTINCT FROM 谓词：

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

对于非空输入，IS DISTINCT FROM 等同于 <> 操作符。但是，如果两个输入都是 null，则返回 false，如果只有一个输入为 null，则返回 true。同样，IS NOT DISTINCT FROM 对于非空输入与 = 相同，但当两个输入都为 null 时返回 true，只有一个输入为 null 时返回 false。因此，这些谓词实际上表现得好像 null 是一个正常的数据值，而不是“未知”。

要检查一个值是否为 null，请使用以下谓词：

expression IS NULL
expression IS NOT NULL

或等效但非标准的谓词：

expression ISNULL
expression NOTNULL

不要写成 expression = NULL，因为 NULL 不等于 “等于” NULL。（null 值表示未知值，不知道两个未知值是否相等。）

提示
有些应用程序可能期望 expression = NULL 在 expression 评估为 null 值时返回 true。强烈建议修改这些应用程序以符合 SQL 标准。但是，如果无法这样做，则可以使用 transform_null_equals 配置变量。如果启用了它，UXsinoDB 将把 x = NULL 子句转换为 x IS NULL。

如果 expression 是行值，则当行表达式本身为 null 或所有行字段都为 null 时，IS NULL 为 true，而当行表达式本身为非 null 且所有行字段都为非 null 时，IS NOT NULL 为 true。由于这种行为，IS NULL 和 IS NOT NULL 并不总是对行值表达式返回相反的结果；特别是，包含 null 和非 null 字段的行值表达式将对两个测试都返回 false。在某些情况下，最好编写 row IS DISTINCT FROM NULL 或 row IS NOT DISTINCT FROM NULL，这将仅检查整个行值是否为 null，而不进行任何其他行字段的测试。

布尔值也可以使用以下谓词进行测试：

boolean_expression IS TRUE
boolean_expression IS NOT TRUE
boolean_expression IS FALSE
boolean_expression IS NOT FALSE
boolean_expression IS UNKNOWN
boolean_expression IS NOT UNKNOWN

这些函数总是返回 true 或 false，即使操作数为 null 时也不会返回 null 值。null 输入被视为逻辑值“unknown”。请注意，IS UNKNOWN 和 IS NOT UNKNOWN 实际上与 IS NULL 和 IS NOT NULL 相同，只是输入表达式必须是布尔类型。

还有一些比较相关的函数，如表 比较函数所示。

表 比较函数

3.1. INTERVAL()

• 功能

  INTERVAL(N, N1, N2, N3...) 函数可以返回 N 在后续输入数列中的位置。

• 函数

  INTERVAL(N, N1, N2, N3...)
  

  如果 N < N1，则返回 0，如果 N1 <= N < N2，则返回 1，如果 N2 <= N < N3，则返回 2。以此类推，至少输入 2 个参数，区间是前闭后开的，对于输入个数少于 8 时逐一比较，大于 8 时使用二分查找算法比较。该函数仅在 mysql 兼容模式支持。

• 示例

  1. 比较结果

     select interval(2.3, 1, 2, 3, 4);
     interval_function
     -------------------
                    2
     

  2. 开头数字为 null 时，返回 -1

     select interval(null, 2, 1, 3, 4);
     interval_function
     -------------------
                    -1
     

  3. 输入数据，null 在被比较数据中作为最小值

     select interval(-23544365472.3, null, 1, 2.2, 4);
     interval_function
     -------------------
                    1
     

  4. 接受表达式的输入

     select interval(3-2, 1, 1+1, 4);
     interval_function
     -------------------
                    1
     

4. 数学函数和操作符

许多 UXsinoDB 类型都提供了数学操作符。对于没有标准数学约定的类型（例如日期/时间类型），在后续章节中描述实际行为。

表 数学操作符显示了标准数值类型可用的数学操作符。除非另有说明，以 numeric_type 为参数的操作符可用于所有类型 smallint、integer、bigint、numeric、real 和 double precision。以 integral_type 为参数的操作符可用于类型 smallint、integer 和 bigint。除非另有说明，每种操作符形式返回与其参数相同的数据类型。涉及多个参数数据类型的调用（例如 integer + numeric）将使用这些列表中后面出现的类型进行解析。

表 数学操作符

表 数学函数显示了可用的数学函数。其中许多函数提供了多种不同的参数类型。除非另有说明，函数的任何给定形式都返回与其参数相同的数据类型；交叉类型的情况解决方式与上述操作符相同。与 double precision 数据一起工作的函数大多是在主机系统的 C 库之上实现的；因此，精度和边界情况下的行为可能因主机系统而异。

表 数学函数

mod(y, x) 函数参数类型支持数字类型及纯数字的字符串类型，不支持空串。

表 随机函数显示了用于生成随机数的函数。

表 随机函数

random() 函数使用确定性伪随机数生成器。它速度快，但不适用于加密应用程序；请参见 uxcrypto 模块以获取更安全的替代方案。如果调用 setseed()，那么当前会话中后续 random() 调用的一系列结果可以通过使用相同参数重新发出 setseed() 来重复。如果在同一个会话中没有任何之前的 setseed() 调用，那么第一个 random() 调用将从依赖于平台的随机位源获得一个种子。

表 三角函数显示了可用的三角函数。每个函数都有两个变体，一个用弧度度量角度，另一个用度数度量角度。

表 三角函数

注意
另一种处理以度数表示的角度的方法是使用前面展示的单位转换函数 radians() 和 degrees()。然而，使用基于度数的三角函数是更好的，该方式避免了特殊情况下的舍入误差，例如 sind(30)。

表 双曲函数显示了可用的双曲函数。

表 双曲函数

5. 字符串函数和操作符

本节介绍用于检查和操作字符串值的函数和操作符。在此上下文中，字符串包括类型为 character、character varying 和 text 的值。除非另有说明，这些函数和操作符被声明为接受和返回类型为 text。它们将互换地接受 character varying 参数。在应用函数或操作符之前，类型为 character 的值将被转换为 text，从而剥离 character 值中的任何尾随空格。

SQL定义了一些使用关键字而不是逗号分隔参数的字符串函数。详细信息请参见表 SQL 字符串函数和操作符。UXsinoDB还提供了使用常规函数调用语法的这些函数的版本（请参见表 其他字符串函数和操作符）。

注意
字符串连接操作符（||）将接受非字符串输入，只要至少有一个输入是字符串类型，如表 SQL 字符串函数和操作符所示。对于其他情况，可以插入显式强制转换为 text，以使非字符串输入被接受。

表 SQL 字符串函数和操作符

还有其他可用的字符串操作函数和操作符，列在表 其他字符串函数和操作符中。（其中一些用于内部实现表 SQL 字符串函数和操作符中列出的 SQL 标准字符串函数。）还有模式匹配操作符，这些在模式匹配中描述，以及全文搜索操作符。

表 其他字符串函数和操作符

concat、concat_ws 和 format 函数是可变参数的，因此可以将要连接或格式化的值作为带有 VARIADIC 关键字标记的数组传递。数组的元素被视为函数的单独普通参数。如果可变数组参数为 NULL，则 concat 和 concat_ws 返回 NULL，但 format 将 NULL 视为零元素数组。

另请参见聚合函数中的 string_agg，以及在表 文本/二进制字符串转换函数中用于在字符串和 bytea 类型之间转换的函数。

5.1. format

函数 format 根据格式字符串生成格式化输出，类似于 C 函数 sprintf。

format(formatstr text [, formatarg "any" [, ...] ])

formatstr 是一个格式字符串，指定结果应该如何格式化。格式字符串中的文本直接复制到结果中，除非使用了格式说明符。格式说明符在字符串中充当占位符，定义如何格式化并插入后续函数参数到结果中。每个 formatarg 参数都按照其数据类型的通常输出规则转换为文本，然后根据格式说明符格式化并插入到结果字符串中。

格式说明符由 % 字符引入，其形式为

%[position][flags][width]type

其中组件字段为：

• position（可选） 格式为 n$ 的字符串，其中 n 是要打印的参数索引。索引 1 表示 formatstr 之后的第一个参数。如果省略 position，则默认使用下一个序列中的参数。

• flags（可选） 控制格式说明符输出格式的其他选项。目前，唯一支持的标志是减号（-），它将导致格式说明符的输出左对齐。除非指定了 width 字段，否则这没有任何影响。

• width（可选） 指定用于显示格式说明符输出的最小字符数。根据需要在左侧或右侧（取决于 - 标志）用空格填充输出以填充宽度。太小的宽度不会导致输出被截断，而只是被忽略。可以使用以下任何一种指定宽度：正整数；星号（*）使用下一个函数参数作为宽度；或形式为 *n$ 的字符串，以使用第 n 个函数参数作为宽度。

  如果宽度来自函数参数，则在用于格式说明符的值的参数之前使用该参数。如果宽度参数为负，则结果将左对齐（就像指定了 - 标志一样）在长度为 abs（width）的字段内。

• type（必需） 用于生成格式说明符输出的格式转换类型。支持以下类型：

  • s 将参数值格式化为简单字符串。空值视为空字符串。
  • I 将参数值视为 SQL 标识符，必要时用双引号括起来。值为空（等效于 quote_ident）是错误的。
  • L 将参数值引用为 SQL 文字。空值显示为字符串 NULL，不带引号（等效于 quote_nullable）。

除了上面描述的格式说明符之外，还可以使用特殊序列 %% 来输出文字 % 字符。

以下是一些基本格式转换的示例：

SELECT format('Hello %s', 'World');
Result: Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
Result: INSERT INTO locations VALUES('C:\Program Files')

以下是使用 width 字段和 - 标志的示例：

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

SELECT format('|%*s|', 10, 'foo');
Result: |       foo|

SELECT format('|%*s|', -10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', 10, 'foo');
Result: |foo       |

SELECT format('|%-*s|', -10, 'foo');
Result: |foo       |

这些示例展示了 position 字段的使用：

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Result: Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Result: |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Result: |       foo|

与标准 C 函数 sprintf 不同，UXsinoDB 的 format 函数允许在同一格式字符串中混合使用带有和不带有 position 字段的格式说明符。没有 position 字段的格式说明符总是使用上一个参数之后的下一个参数。此外，format 函数不要求在格式字符串中使用所有函数参数。例如：

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Result: Testing three, two, three

%I 和 %L 格式说明符特别适用于安全地构建动态 SQL 语句。

5.2. btrim

• 功能

  btrim 函数可以去除 expr 头尾的指定 chr。调用时只有 expr 而不指定 chr 时，可以去除 expr 头尾的空格。

• 函数

  btrim(expr[, chr])
  

• 参数

  表 btrim 参数说明

  参数	说明
  expr	一个表中已经存在的列名。
  chr	指定字符。

• 返回值

  text

• 示例

  select btrim(' abcda ') from dual;
  select btrim(' abcda ', 'a') from dual; 
  

• 注意事项

  此函数需要加载 oracle 插件。 此函数不支持 blob 和 clob 等大对象。

5.3. group_concat

• 功能

  对多行内容做字符串拼接，默认以 ',' 分隔开，但是可以指定。有多个参数时会将每一行先做一个字符串拼接中间无间隔；再对每行拼接以分隔符拼接。

• 函数

  GROUP_CONCAT([DISTINCT] cat_arg1 [,cat_arg2 ...]
               [ORDER BY {unsigned_integer | col_name | expr}
                [ASC | DESC] [,col_name ...]]
               [SEPARATOR str_val])
  

  注意
  DISTINCT、ORDER BY 不能同时使用。

• 参数

  表 group_concat 参数说明

  参数	说明
  cat_arg	连接内容参数，1 个或多个；可以为能转换为字符的任意类型。
  SEPARATOR str_val	用于指定间隔符号，可以是空串，但是不能为空；只能是单引号常量（即字符），当省略这个字段时间默认为 ','。

• 返回值

  返回连接后的字符串，类型为 text。

  注意

  1. 一行中有一列为空，那一行会被忽略。
  2. 参数 group_concat_max_len 用于指定拼接结果的最大长度，连接后的字符串超过时按照 group_concat_max_len 指定的长度截断（最小值为 4，设置小于 4 的值时，不会报错，实际会设置为 4）；字符串小于最大限制时不会补充空格。

• 示例

  uxdb=# CREATE TABLE products (product_no integer, name text, price numeric);
  CREATE TABLE
  uxdb=# INSERT INTO products VALUES(1, 'Cheese', 9.99),(2, 'Bread', 1.99),(3, 'Milk', 2.99);
  INSERT 0 3
  uxdb=# select group_concat(product_no, name) from products;
      group_concat
  ----------------------
   1Cheese,2Bread,3Milk
  (1 row)
  uxdb=# select group_concat(product_no, name separator '+') from products;
      group_concat
  ----------------------
   1Cheese+2Bread+3Milk
  (1 row)
  

5.4. substr

• 功能

  substr 截取字符串或字符串表达式。

• 函数

  substr(A, B, C);
  substr(A, B);
  substr(A from B for C);
  substr(A from B);
  substr(A for C);
  substr(A for C from B);
  

• 参数

  表 substr 参数说明

  参数	说明
  A	需要被截取的字符串或字符串表达式。
  B	从第 B 个字符开始截取。
  C	截取长度为 C 的字符串信息。

• 返回值

  text

• 示例

  select substr('tesabcdefg', 2, 5);
  select substr('tesabcdefg', 2);
  select substr('tesabcdefg' from 2 for 5); 
  select substr('tesabcdefg' from 2); 
  select substr('tesabcdefg' for 5); 
  select substr('tesabcdefg' for 5 from 2);
  

5.5. unhex

• 功能

  根据输入的字符串，每对字符理解为十六进制数字并返回其对应的 ASCII 码字符。 字符必须是合法的十六进制数字，否则返回 NULL（空）。 输入奇数个字符时，会在字符串左侧补 0。

• 函数

  unhex(str);
  unhex(number);
  

  返回显示效果由 binary-as-hex 参数控制。

  启动数据库时若 binary-as-hex = true，则 unhex 返回值显示为二进制字符串。 启动数据库时若 binary-as-hex = false，则 unhex 返回值显示为 ASCII 字符。

• 返回值

  varbinary

• 示例

  uxdb=> select unhex('55584442');
    unhex
  ------------
  0x55584442
  (1 row)
  
  uxdb=> select unhex(55584442);
    unhex
  ------------
  0x55584442
  (1 row)
  

5.6. || 操作符

1. 双竖线操作符作为字符连接操作符功能使用，如下所示。

   函数	返回值	描述	示例
   string || string	text	串接	'UXsino' || 'DB' → UXsinoDB
   string || non-string 或 non-string || string	text	使用一个非字符串输入的串接	'Value: ' || 42 → Value: 42
   varbinary || varbinary	varbinary	串接
   binary || binary	varbinary	串接

2. mysql 模式下为 sql_mode 添加 PIPES_AS_CONCAT 参数，|| 操作符转为逻辑或功能使用。

   select 1 || 0;
   
    ?column?
   ---------- 
    t                                                                                               
   (1 row)                                                                                                      
   
   select ux_typeof(1 || 0);
   
   ux_typeof
   ----------- 
   boolean                                                                                                      
   (1 row) 
   

5.7. insert()

• 功能

  insert() 函数返回 str 字符串从位置 pos 开始剪切，len 字符长被字符串 newstr 替换的新字符串。

  如果 pos 不在字符串的长度范围内，则返回原始字符串。 如果 len 不在字符串其余部分的长度范围内，则从位置 pos 替换字符串的其余部分。 如果任何参数为 NULL，则返回 NULL。

• 函数

  INSERT (str, pos, len, newstr)
  

• 示例

  uxdb=# SELECT INSERT ('Quadratic', 3, 4, 'What');
    insert
  -----------
  QuWhattic
  (1 row)
  
  uxdb=# SELECT INSERT ('Quadratic', -1, 4, 'What');
    insert
  -----------
  Quadratic
  (1 row)
  
  uxdb=# SELECT INSERT ('Quadratic', 3, 100, 'What');
  insert
  --------
  QuWhat
  (1 row)
  

5.8. NLS

支持 Oracle 的 NLS 系列函数 NLS_LOWER，NLS_UPPER，NLS_INITCAP。

5.8.1. NLS_LOWER

• 功能

  返回将字符串并将其变成小写，按 y 指定的排序规则输出。 如果不带参数 y，则函数的功能和对应的去掉 NLS 前缀的函数效果一样，例如 NLS_LOWER(x) 和 LOWER(x) 一样。

• 函数

  NLS_LOWER(x[, y])
  

• 参数

  表 NLS_LOWER 参数说明

  参数	说明
  x	text 类型字符或者可以转换成 text 类型的数据。
  y	nls_parameter，可选的 NLS 参数，格式为 nls_sort= ...，后面的内容可以指定 ux_collation 中 collname 的所有字段。

  参数 y 需要以 nls_sort= 为开头，不区分大小写，可以有空格。 如果 x 和 y 至少有一项为 NULL，则函数会直接返回 NULL。

• 示例

  uxdb=# select nls_lower('AC','nls_sort=C');
  nls_lower
  -----------
  ac
  (1 row)  
  

5.8.2. NLS_UPPER

• 功能

  返回将字符串并将其变成大写，按 y 指定的排序规则输出。

• 函数

  NLS_UPPER(x[, y])
  

• 参数

  表 NLS_UPPER 参数说明

  参数	说明
  x	text 类型字符或者可以转换成 text 类型的数据。
  y	nls_parameter，可选的 NLS 参数，格式为 nls_sort= ...，后面的内容可以指定 ux_collation 中 collname 的所有字段。

  参数 y 需要以 nls_sort= 为开头，不区分大小写，可以有空格。 如果 x 和 y 至少有一项为 NULL，则函数会直接返回 NULL。

• 示例

  uxdb=# select nls_upper('ab','NLS_SORT=zh_SG');
  nls_upper
  -----------
  AB
  (1 row)
  

5.8.3. NLS_INITCAP

• 功能

  返回将字符串并将其变成首字母大写其他小写的格式，按 y 指定的排序规则输出。

• 函数

  NLS_INITCAP(x[, y])
  

• 参数

  表 NLS_INITCAP 参数说明

  参数	说明
  x	text 类型字符或者可以转换成 text 类型的数据。
  y	nls_parameter，可选的 NLS 参数，格式为 nls_sort= ...，后面的内容可以指定 ux_collation 中 collname 的所有字段。

  参数 y 需要以 nls_sort= 为开头，不区分大小写，可以有空格。 如果 x 和 y 至少有一项为 NULL，则函数会直接返回 NULL。

• 示例

  uxdb=# select nls_initcap('aAadf', NULL);
  nls_initcap
  -------------
  
  (1 row)
  
  uxdb=# select nls_initcap('aAadf','nls _so r t=C');
  nls_initcap
  -------------
  Aaadf
  (1 row)
  

5.9. MID

• 功能

  MID 函数是 MySQL 的字符串截取函数。此函数用于返回一个字符串中从指定位置开始的指定长度的子字符串。MID(str, pos, len) 等同于 SUBSTRING(str, pos, len) 和 SUBSTR(str, pos, len) 函数。

• 函数

  MID(str, pos, len)
  

• 参数

  表 MID 参数说明

  参数	说明
  str (text 类型)	必需的。从中提取子字符串的原字符串。
  pos (int 类型)	必需的。开始提取的位置。它可以是正数或者负数。如果是正数，则从字符串的开头开始提取子字符串。如果是负数，则从字符串的结尾提取子字符串。
  len (int 类型)	可选的。子字符串的长度（字符数）。如果不指定，则提取到原字符串的结尾。

• 返回值

  当 pos 为 0 或者超过原字符串的长度时，MID() 函数将返回空串。当参数为 NULL 时，MID() 函数将返回 NULL。

• 示例

  uxdb=# select mid('hello', 0);
   mid
  -----
  
  (1 row)
  
  uxdb=# select mid('hello', 3);
   mid
  -----
   llo
  (1 row)
  
  uxdb=# select mid('hello', 8);
   mid
  -----
  
  (1 row)
  
  uxdb=# select mid('hello', 2, 3);
   mid
  -----
   ell
  (1 row)
  
  uxdb=# select mid('hello', -1, 3);
   mid
  -----
   o
  (1 row)
  
  uxdb=# select mid('hello', -4, 3);
   mid
  -----
   ell
  (1 row)
  

6. 二进制字符串函数和操作符

本节介绍用于检查和操作二进制字符串的函数和操作符，即类型为 bytea 的值。其中许多在目的和语法上等效于前一节中描述的文本字符串函数。

SQL定义了一些字符串函数，使用关键字而不是逗号来分隔参数。详情请参见表 SQL 二进制字符串函数和操作符。UXsinoDB还提供了这些函数的版本，使用常规函数调用语法（请参见表 其他二进制字符串函数）。

表 SQL 二进制字符串函数和操作符

还有其他可用的二进制字符串操作函数，列在表 其他二进制字符串函数中。其中一些用于内部实现列在表 SQL 二进制字符串函数和操作符中的 SQL 标准字符串函数。

表 其他二进制字符串函数

函数 get_byte 和 set_byte 将二进制字符串的第一个字节编号为字节 0。函数 get_bit 和 set_bit 在每个字节内从右侧编号位；例如，位 0 是第一个字节的最低有效位，位 15 是第二个字节的最高有效位。

由于历史原因，函数 md5 返回类型为 text 的十六进制编码值，而 SHA-2 函数返回类型为 bytea。使用函数 encode 和 decode 在两者之间进行转换。例如，写 encode(sha256('abc'), 'hex') 以获得十六进制编码的文本表示，或 decode(md5('abc'), 'hex') 以获得 bytea 值。

将字符串在不同字符集（编码）之间转换的函数，以及用文本形式表示任意二进制数据的函数，如表 文本/二进制字符串转换函数所示。对于这些函数，类型为 text 的参数或结果以数据库的默认编码表示，而类型为 bytea 的参数或结果则以另一个参数命名的编码表示。

表 文本/二进制字符串转换函数

encode 和 decode 函数支持以下文本格式：

• base64：base64 格式是 RFC 2045 第 6.8 节的格式。根据 RFC，编码行在 76 个字符处中断。但是，换行符只用于行末，而不是 MIME CRLF 行末标记。decode 函数忽略回车符、换行符、空格和制表符。否则，当 decode 提供无效的 base64 数据时，包括尾随填充不正确时，会引发错误。

• escape：escape 转义格式将零字节和高位设置的字节转换为八进制转义序列（\nnn），并加倍反斜杠。其他字节值按字面表示。如果反斜杠后面没有跟第二个反斜杠或三个八进制数字，则 decode 函数将引发一个错误；它接受其他字节值保持不变。

• hex：hex 格式将每 4 位数据表示为一个十六进制数字，0 到 f，先写入每个字节的高位数字。encode 函数以小写形式输出 a-f 十六进制数字。因为最小的数据单位是 8 位，所以 encode 始终返回偶数个字符。decode 函数接受 a-f 字符的大写或小写形式。当 decode 给出无效的十六进制数据时（包括给出奇数个字符时），将引发错误。

另请参见聚合函数中的 string_agg。

7. 位串函数和操作符

本节介绍用于检查和操作位串的函数和操作符，即类型 bit 和 bit varying 的值。（虽然这些表中只提到了 bit 类型，但 bit varying 类型的值可以互换使用。）位串支持表 比较操作符中显示的常规比较操作符，以及表 位串操作符中显示的操作符。

表 位串操作符

一些适用于二进制字符串的函数也适用于位字符串，如表 位字符串函数所示。

表 位字符串函数

此外，可以将整数值强制转换为类型 bit。将整数转换为 bit(n) 会复制最右边的 n 位。将整数转换为比整数本身宽的位字符串将在左侧进行符号扩展。一些例子：

44::bit(10)                    0000101100
44::bit(3)                     100
cast(-44 as bit(12))           111111010100
'1110'::bit(4)::integer        14

请注意，仅强制转换为“bit”意味着转换为 bit(1)，因此仅会提供整数的最低有效位。

8. 模式匹配

UXsinoDB 提供了三种不同的模式匹配方法：传统的 SQL LIKE 操作符、较新的 SIMILAR TO 操作符（在 SQL:1999 中添加），以及 POSIX 风格的正则表达式。除了基本的“这个字符串是否与这个模式匹配？”操作符外，还有可用于提取或替换匹配子字符串以及在匹配位置拆分字符串的函数。

提示
如果您的模式匹配需求超出了这个范围，请考虑使用 Perl 或 Tcl 编写用户定义函数。

注意
虽然大多数正则表达式搜索可以非常快速地执行，但是可以构造需要任意量的时间和内存来处理的正则表达式。谨慎接受来自敌对来源的正则表达式搜索模式。如果必须这样做，建议设置语句超时。

使用 SIMILAR TO 模式进行搜索具有相同的安全风险，因为 SIMILAR TO 提供了与 POSIX 风格的正则表达式相同的功能。

与其他两个选项相比，LIKE 搜索更简单，因此在可能存在敌对模式来源的情况下更安全。

所有三种类型的模式匹配操作符都不支持非确定性排序规则。如果需要，请将不同的排序规则应用于表达式以解决此限制。

8.1. LIKE

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

LIKE 表达式返回 true，如果 string 与提供的 pattern 匹配。（如预期的，如果 LIKE 返回 true，则 NOT LIKE 表达式返回 false，反之亦然。等价的表达式是 NOT (string LIKE pattern)。）

如果 pattern 不包含百分号或下划线，则该模式仅表示字符串本身；在这种情况下，LIKE 的作用类似于等于操作符。下划线（_）在 pattern 中代表（匹配）任何单个字符；百分号（%）匹配任何零个或多个字符的序列。

一些例子：

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

LIKE 模式匹配始终覆盖整个字符串。因此，如果希望在字符串的任何位置匹配序列，则模式必须以百分号开头和结尾。

要匹配字面下划线或百分号而不匹配其他字符，必须在 pattern 中的相应字符之前加上转义字符。默认的转义字符是反斜杠，但可以通过使用 ESCAPE 子句选择不同的转义字符。要匹配转义字符本身，请写两个转义字符。

注意
如果您关闭了 standard_conforming_strings，则在文本字符串常量中编写的任何反斜杠都需要加倍。

还可以通过编写 ESCAPE '' 来选择不使用转义字符。这实际上禁用了转义机制，这使得无法关闭模式中下划线和百分号的特殊含义。

根据 SQL 标准，省略 ESCAPE 意味着没有转义字符（而不是默认为反斜杠），并且不允许使用长度为零的 ESCAPE 值。UXsinoDB 在这方面的行为因此略有不同于标准。

关键字 ILIKE 可以用来代替 LIKE，根据活动语言环境使匹配不区分大小写。这不是 SQL 标准，但是是 UXsinoDB 的扩展。

操作符 ~~ 等同于 LIKE，~~* 对应于 ILIKE。还有 !~~ 和 !~~* 操作符，分别表示 NOT LIKE 和 NOT ILIKE。所有这些操作符都是 UXsinoDB 特有的。您可能会在 EXPLAIN 输出和类似的地方看到这些操作符名称，因为解析器实际上将 LIKE 等转换为这些操作符。

在 UXsinoDB 语法中，短语 LIKE、ILIKE、NOT LIKE 和 NOT ILIKE 通常被视为操作符；例如，它们可以在 expression operator ANY (subquery) 构造中使用，尽管 ESCAPE 子句不能在其中包含。在某些晦涩的情况下，可能需要使用底层操作符名称。

还请参阅以 ^@ 开头的操作符和相应的 starts_with() 函数，它们在需要仅匹配字符串开头的情况下非常有用。

表 LIKE 支持参数类型

8.2. SIMILAR TO 正则表达式

string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

SIMILAR TO 操作符根据其模式是否与给定字符串匹配返回 true 或 false。它类似于 LIKE，但它使用 SQL 标准的正则表达式定义来解释模式。SQL 正则表达式是 LIKE 符号和常见（POSIX）正则表达式符号之间的奇特交叉。

与 LIKE 一样，SIMILAR TO 操作符仅在其模式与整个字符串匹配时才成功；这与常见的正则表达式行为不同，其中模式可以匹配字符串的任何部分。与 LIKE 一样，SIMILAR TO 使用 _ 和 % 作为通配符字符，分别表示任何单个字符和任何字符串（这些与 POSIX 正则表达式中的 . 和 .* 相当）。

除了从 LIKE 借用的这些设施外，SIMILAR TO 还支持从 POSIX 正则表达式中借用的这些模式匹配元字符：

• | 表示交替（两个备选项之一）。
• * 表示重复前一个项目零次或多次。
• + 表示重复前一个项目一次或多次。
• ? 表示前一个字符重复零次或一次。
• { m } 表示前一个字符重复 m 次。
• { m ,} 表示前一个字符重复 m 次或更多次。
• { m , n } 表示前一个字符至少重复 m 次，但不超过 n 次。
• 括号 () 可以用于将项分组为单个逻辑项。
• 方括号 [...] 指定一个字符类，就像在 POSIX 正则表达式中一样。

请注意，句点（.）不是 SIMILAR TO 的元字符。

与 LIKE 一样，反斜杠可以禁用任何这些元字符的特殊含义。可以使用不同的转义字符指定 ESCAPE，或者通过编写 ESCAPE '' 来禁用转义功能。

根据 SQL 标准，省略 ESCAPE 表示没有转义字符（而不是默认为反斜杠），并且不允许使用零长度的 ESCAPE 值。因此，UXsinoDB 在这方面的行为略有不同。

另一个非标准扩展是，在转义字符后面跟一个字母或数字可以访问为 POSIX 正则表达式定义的转义序列；请参见表 正则表达式字符转义、表 正则表达式类别缩写转义和表 正则表达式约束转义。

一些示例：

'abc' SIMILAR TO 'abc'          true
'abc' SIMILAR TO 'a'            false
'abc' SIMILAR TO '%(b|d)%'      true
'abc' SIMILAR TO '(b|c)%'       false
'-abc-' SIMILAR TO '%\mabc\M%'  true
'xabcy' SIMILAR TO '%\mabc\M%'  false

带有三个参数的 substring 函数提供了匹配 SQL 正则表达式模式的子字符串提取。该函数可以按照标准 SQL 语法编写：

substring(string similar pattern escape escape-character)

或使用现已过时的 SQL:1999 语法：

substring(string from pattern for escape-character)

也可以使用三个参数的普通函数形式：

substring(string, pattern, escape-character)

与 SIMILAR TO 一样，指定的模式必须完全匹配数据字符串，否则函数将失败并返回 null。为了指示匹配数据子字符串感兴趣的模式部分，模式应包含两个转义字符后跟双引号（"）。成功匹配时返回在这些分隔符之间的模式部分的文本匹配。转义双引号分隔符实际上将 substring 的模式分成三个独立的正则表达式；例如，在任何三个部分中的竖线（|）仅影响该部分。此外，当有关数据字符串匹配哪个模式的多少存在任何歧义时，这三个正则表达式中的第一个和第三个被定义为匹配最小可能的文本，而不是最大可能的文本。（在 POSIX 术语中，第一个和第三个正则表达式被强制为非贪婪的。）

作为 SQL 标准的扩展，UXsinoDB 允许只有一个转义双引号分隔符，在这种情况下，第三个正则表达式被视为空；或没有分隔符，在这种情况下，第一个和第三个正则表达式被视为空。

以下是一些示例，其中 #" 用于分隔返回字符串：

substring('foobar' similar '%#"o_b#"%' escape '#')   oob
substring('foobar' similar '#"o_b#"%' escape '#')    NULL

8.3. POSIX 正则表达式

表 正则表达式匹配操作符列出了使用 POSIX 正则表达式进行模式匹配的可用操作符。

表 正则表达式匹配操作符

POSIX 正则表达式提供了比 LIKE 和 SIMILAR TO 操作符更强大的模式匹配方式。许多 Unix 工具，如 egrep、sed 或 awk 使用与此处描述的类似的模式匹配语言。

正则表达式是一个字符序列，是一组字符串（一个正则集）的缩写定义。如果一个字符串是由正则表达式描述的正则集的成员，则称该字符串与正则表达式匹配。与 LIKE 一样，除非它们是正则表达式语言中的特殊字符，否则模式字符与字符串字符完全匹配，但正则表达式使用不同于 LIKE 的特殊字符。与 LIKE 模式不同的是，正则表达式允许匹配字符串中的任何位置，除非正则表达式明确地锚定到字符串的开头或结尾。

一些例子：

'abcd' ~ 'bc'     true
'abcd' ~ 'a.c'    true — 点号匹配任何字符
'abcd' ~ 'a.*d'   true — * 重复前面的模式项
'abcd' ~ '(b|x)'  true — | 表示或，括号分组
'abcd' ~ '^a'     true — ^ 锚定到字符串的开头
'abcd' ~ '^(b|c)' false — 除了锚定，会匹配

POSIX 模式语言在下面有更详细的描述。

带有两个参数的 substring 函数 substring(string from *pattern* ) 提供了提取与 POSIX 正则表达式模式匹配的子字符串。如果没有匹配，则返回 null，否则返回匹配模式的文本的第一部分。但是，如果模式包含任何括号，则返回匹配第一个括号子表达式（左括号最先出现的子表达式）的文本部分。如果您想在其中使用括号而不触发此异常，可以将括号放在整个表达式周围。如果您需要在要提取的子表达式之前在模式中使用括号，请参见下面描述的非捕获括号。

一些例子：

substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o

regexp_count 函数计算 POSIX 正则表达式模式在字符串中匹配的次数。它的语法为 regexp_count(string, pattern [, start [, flags ]])。pattern 在 string 中进行搜索，通常从字符串的开头开始，但如果提供了 start 参数，则从该字符索引开始。flags 参数是一个可选的文本字符串，其中包含零个或多个单字母标志，可以更改函数的行为。例如，在 flags 中包含 i 指定不区分大小写匹配。支持的标志在表 ARE 嵌入选项字母中描述。

一些例子：

regexp_count('ABCABCAXYaxy', 'A.')          3
regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  4

regexp_instr 函数返回 POSIX 正则表达式模式与字符串匹配的第 N 个匹配的起始或结束位置，如果没有这样的匹配，则返回零。它的语法为 regexp_instr(string, pattern [, start [, N [, endoption [, flags [, subexpr ]]]]])。pattern 在 string 中进行搜索，通常从字符串的开头开始，但如果提供了 start 参数，则从该字符索引开始。如果指定了 N，则定位模式的第 N 个匹配，否则定位第一个匹配。如果省略 endoption 参数或将其指定为零，则函数返回匹配的第一个字符的位置。否则，endoption 必须为一，函数返回匹配后面的字符的位置。flags 参数是一个可选的文本字符串，其中包含零个或多个单字母标志，可以更改函数的行为。支持的标志在表 ARE 嵌入选项字母中描述。对于包含括号子表达式的模式，subexpr 是一个整数，指示感兴趣的子表达式：结果标识与该子表达式匹配的子字符串的位置。子表达式按其前导括号的顺序编号。当省略或设置为零时，subexpr 的结果将标识整个匹配的位置，而不考虑括号子表达式。

以下是一些示例：

regexp_instr('number of your street, town zip, FR', '[^,]+', 1, 2) → 23
regexp_instr('ABCDEFGHI', '(c..)(...)', 1, 1, 0, 'i', 2) → 6

regexp_like 函数检查 POSIX 正则表达式模式是否在字符串中出现匹配，返回布尔值 true 或 false。它的语法为 regexp_like(string, pattern [, flags ])。flags 参数是一个可选的文本字符串，包含零个或多个单字母标志，用于更改函数的行为。支持的标志在表 ARE 嵌入选项字母中描述。如果未指定标志，则此函数与 ~ 操作符具有相同的结果。如果仅指定了 i 标志，则其结果与 ~* 操作符相同。

以下是一些示例：

regexp_like('Hello World', 'world')       false
regexp_like('Hello World', 'world', 'i')  true

regexp_match 函数返回一个文本数组，其中包含与字符串中 POSIX 正则表达式模式的第一个匹配项相匹配的子字符串。它的语法为 regexp_match(string, pattern [, flags ])。如果没有匹配项，则结果为 NULL。如果找到匹配项，并且 pattern 不包含括号子表达式，则结果是一个包含与整个模式匹配的子字符串的单个元素文本数组。如果找到匹配项，并且 pattern 包含括号子表达式，则结果是一个文本数组，其中第 n 个元素是与 pattern 的第 n 个括号子表达式匹配的子字符串（不包括“非捕获”括号；有关详细信息，请参见下文）。flags 参数是一个可选的文本字符串，包含零个或多个单字母标志，用于更改函数的行为。支持的标志在表 ARE 嵌入选项字母中描述。

以下是一些示例：

SELECT regexp_match('foobarbequebaz', 'bar.*que');
 regexp_match
--------------
 {barbeque}
(1 row)

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
 regexp_match
--------------
 {bar,beque}
(1 row)

提示
在通常情况下，如果您只想要整个匹配的子字符串或无匹配项的 NULL，最好的解决方案是使用 regexp_substr()。但是，regexp_substr() 仅存在于 UXsinoDB 2122 及以上版本中。在旧版本中工作时，您可以提取第一个元素。regexp_match() 的结果，例如：

SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
regexp_match
--------------
barbeque
(1 row)

regexp_matches 函数返回一个文本数组集合，其中包含与 POSIX 正则表达式模式匹配的子字符串的匹配。它具有与 regexp_match 相同的语法。如果没有匹配，则此函数不返回行，如果有匹配且未给出 g 标志，则返回一行，如果有 N 个匹配并且给出了 g 标志，则返回 N 行。每个返回的行都是一个文本数组，其中包含整个匹配的子字符串或与 pattern 的括号子表达式匹配的子字符串，就像上面为 regexp_match 所描述的那样。regexp_matches 接受在表 ARE 嵌入选项字母中显示的所有标志，以及命令它返回所有匹配项而不仅仅是第一个匹配项的 g 标志。

一些例子：

SELECT regexp_matches('foo', 'not there');
 regexp_matches
----------------
(0 rows)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

提示
在大多数情况下，应使用带有 g 标志的 regexp_matches()，因为如果您只想要第一个匹配项，则使用 regexp_match() 更容易和更有效。但是，regexp_match() 仅存在于 UXsinoDB 版本 2112 及以上版本中。在旧版本中工作时，常见的技巧是将 regexp_matches() 调用放在子选择中，例如：

SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;

如果有匹配，则会产生文本数组，如果没有匹配，则会产生 NULL，与 regexp_match() 的行为相同。如果没有子选择，则对于没有匹配的表行，此查询将根本不产生输出，这通常不是所需的行为。

regexp_replace 函数提供了对与 POSIX 正则表达式模式匹配的子字符串进行替换的新文本的替换。它的语法为 regexp_replace(source, pattern, replacement [, start [, N ]] [, flags ])。（请注意，除非指定了 start，否则无法指定 N，但可以在任何情况下给出 flags。）如果没有与 pattern 匹配的内容，则返回 source 字符串。如果有匹配，则返回 source 字符串，其中 replacement 字符串替换了匹配的子字符串。replacement 字符串可以包含 \n，其中 n 是 1 到 9，指示应插入与模式的第 n 个括号子表达式匹配的源子字符串，并且它可以包含 \& 以指示应插入与整个模式匹配的子字符串。如果需要在替换文本中放置文字反斜杠，请写 \\。在字符串中搜索模式 pattern，通常从字符串开头开始，但如果提供了 start 参数，则从该字符索引开始。默认情况下，仅替换模式的第一个匹配项。如果指定了 N 并且大于零，则替换模式的第 N 个匹配项。如果给出了 g 标志，或者如果指定的 N 为零，则替换从 start 位置或之后的所有匹配项。（当指定 N 时，g 标志被忽略。）flags 参数是一个可选的文本字符串，其中包含零个或多个单字母标志，这些标志改变函数的行为。支持的标志（尽管不包括 g）在表 ARE 嵌入选项字母中描述。

一些例子：

regexp_replace('foobarbaz', 'b..', 'X')
                                   fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
                                   fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g')
                                   fooXarYXazY
regexp_replace('A UXsinoDB function', 'a|e|i|o|u', 'X', 1, 0, 'i')
                                   X UXsXnXDB fXnctXXn
regexp_replace('A UXsinoDB function', 'a|e|i|o|u', 'X', 1, 3, 'i')
                                   A UXsinXDB function

regexp_split_to_table 函数使用 POSIX 正则表达式模式作为分隔符来拆分字符串。它的语法为 regexp_split_to_table(string, pattern[, flags])。如果没有与模式匹配的内容，则函数返回字符串。如果至少有一个匹配项，对于每个匹配项，它返回从最后一个匹配项（或字符串的开头）到匹配项的开头的文本。当没有更多匹配项时，它返回从最后一个匹配项的结尾到字符串的结尾的文本。flags 参数是一个可选的文本字符串，其中包含零个或多个单字母标志，这些标志改变函数的行为。regexp_split_to_table 支持在表 ARE 嵌入选项字母中描述的标志。

regexp_split_to_array 函数的行为与 regexp_split_to_table 相同，只是 regexp_split_to_array 将其结果作为 text 数组返回。它的语法为 regexp_split_to_array(string, pattern[, flags])。参数与 regexp_split_to_table 相同。

以下是一些示例：

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo
-------
 the
 quick
 brown
 fox
 jumps
 over
 the
 lazy
 dog
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo
-----
 t
 h
 e
 q
 u
 i
 c
 k
 b
 r
 o
 w
 n
 f
 o
 x
(16 rows)

正如最后一个示例所示，regexp 分割函数会忽略出现在字符串开头或结尾或紧接在前一个匹配项之后的零长度匹配项。这与其他 regexp 函数实现的严格匹配定义相反，但在实践中通常是最方便的行为。其他软件系统（如 Perl）使用类似的定义。

regexp_substr 函数返回与 POSIX 正则表达式模式匹配的子字符串，如果没有匹配，则返回 NULL。它的语法是 regexp_substr(string, pattern [, start [, N [, flags [, subexpr ]]]])。在 string 中搜索 pattern，通常从字符串开头开始，但如果提供了 start 参数，则从该字符索引开始。如果指定了 N，则返回模式的第 N 个匹配项，否则返回第一个匹配项。flags 参数是一个可选的文本字符串，包含零个或多个单字母标志，用于更改函数的行为。支持的标志在表 ARE 嵌入选项字母中描述。对于包含括号子表达式的模式，subexpr 是一个整数，表示感兴趣的子表达式：结果是匹配该子表达式的子字符串。子表达式按其前导括号的顺序编号。当省略或为零时，结果是整个匹配项，而不考虑括号子表达式。

以下是一些示例：

regexp_substr('number of your street, town zip, FR', '[^,]+', 1, 2) → 'town zip'
regexp_substr('ABCDEFGHI', '(c..)(...)', 1, 1, 'i', 2) → 'FGH'

8.3.1. 正则表达式细节

正则表达式是使用 Henry Spencer 编写的软件包实现的。下面的正则表达式描述大部分是从他的手册中直接复制的。

UXsinoDB 的正则表达式使用 POSIX 1003.2 定义的两种形式：扩展正则表达式或 ERE（大致与 egrep 相同），以及基本的正则表达式或 BRE（大致与 ed 相同）。UXsinoDB 支持这两种形式，并实现了一些不在 POSIX 标准中的扩展，但由于它们在编程语言（如 Perl 和 Tcl）中的可用性而变得广泛使用。在这份文档中，使用这些非 POSIX 扩展的正则表达式称为高级正则表达式或 ARE。ARE 几乎是 ERE 的一个完全超集，但 BRE 有几个符号不兼容（以及更加有限）。我们首先描述 ARE 和 ERE 形式，注意仅适用于 ARE 的功能，然后描述 BRE 的区别。

注意
UXsinoDB 始终最初假定正则表达式遵循 ARE 规则。但是，可以通过在 RE 模式前面添加嵌入选项来选择更有限的 ERE 或 BRE 规则，如正则表达式元语法所述。这对于与期望完全符合 POSIX 1003.2 规则的应用程序兼容非常有用。

正则表达式定义为一个或多个由 | 分隔的分支。它匹配与分支之一匹配的任何内容。

一个分支是零个或多个量化的原子或约束条件，串联在一起。它匹配第一个的匹配，后跟第二个的匹配等等；空分支匹配空字符串。

量化原子是一个可能后跟单个量化器的原子。没有量化器，它匹配原子的匹配。使用量化器时，它可以匹配一些原子的匹配。原子可以是表 正则表达式原子中显示的任何可能性。

约束条件匹配空字符串，但仅在满足特定条件时匹配。约束条件可以在可以使用原子的地方使用，但不能后跟量化器。简单的约束条件如表 正则表达式约束条件所示；稍后将描述一些其他约束条件。

表 正则表达式原子

RE 不能以反斜杠（\）结尾。

注意
如果您关闭了 standard_conforming_strings，您在文字字符串常量中编写的任何反斜杠都需要加倍。

表 正则表达式量词

使用 {...} 的形式被称为边界。边界中的数字 m 和 n 是无符号十进制整数，其可取值范围为 0 到 255（包括 0 和 255）。

非贪婪量词（仅在 ARE 中可用）匹配与其对应的普通（贪婪）量词相同的可能性，但是它们更喜欢匹配最少的次数而不是最多的次数。

注意
量词不能紧接着另一个量词，例如，** 是无效的。量词不能开始一个表达式或子表达式，也不能跟在 ^ 或 | 后面。

表 正则表达式约束条件

前瞻和后顾约束不能包含反向引用（参见正则表达式转义），并且其中的所有括号都被视为非捕获。

8.3.2. 括号表达式

括号表达式是一个包含在 [] 中的字符列表。它通常匹配列表中的任何单个字符（参见下文）。如果列表以 ^ 开头，则匹配列表中其余部分中的任何单个字符不是。如果列表中的两个字符被 - 分隔，则这是这两个字符之间（包括）的所有字符的完整范围的简写，例如，在 ASCII 中，[0-9] 匹配任何十进制数字。两个范围共享一个端点是非法的，例如，a-c-e。范围非常依赖于排序顺序，因此可移植程序应避免依赖它们。

要在列表中包含文字 ]，请将其作为第一个字符（在使用 ^ 的情况下）。要包含文字 -，请将其作为第一个或最后一个字符，或作为范围的第二个端点。要将文字 - 用作范围的第一个端点，请将其括在 [. 和 .] 中，使其成为排序元素（见下文）。除了这些字符、一些使用 [ 的组合（见下一段）和转义（仅适用于 AREs）之外，所有其他特殊字符在括号表达式中失去其特殊意义。特别地，在遵循 ERE 或 BRE 规则时，\ 不是特殊的，尽管在 AREs 中它是特殊的（作为引入转义）。

在括号表达式中，用 [. 和 .] 括起来的排序元素（一个字符、一个按照单个字符排序的多字符序列或一个排序序列名称）代表该排序元素的字符序列。该序列被视为括号表达式列表的单个元素。这允许包含多字符排序元素的括号表达式匹配多个字符，例如，如果排序序列包括一个 ch 排序元素，则 RE [[.ch.]]*c 匹配 chchcc 的前五个字符。

注意
UXsinoDB 目前不支持多字符排序元素。此信息描述了可能的未来行为。

在括号表达式中，用 [= 和 =] 括起来的排序元素是一个等价类，代表所有等价于该元素的所有排序元素的字符序列，包括它本身。（如果没有其他等价的排序元素，则处理方式就像封闭分隔符是 [. 和 .]。）例如，如果 o 和 ^ 是等价类的成员，则 [[=o=]]、[[=^=]] 和 [o^] 都是同义词。一个等价类不能作为范围的端点。

在方括号表达式中，用 [: 和 :] 括起来的字符类名称表示属于该类的所有字符的列表。字符类不能用作范围的端点。POSIX 标准定义了这些字符类名称：alnum（字母和数字）、alpha（字母）、blank（空格和制表符）、cntrl（控制字符）、digit（数字）、graph（可打印字符，不包括空格）、lower（小写字母）、print（可打印字符，包括空格）、punct（标点符号）、space（任何空格）、upper（大写字母）和 xdigit（十六进制数字）。这些标准字符类的行为通常在 7 位 ASCII 字符集中的字符在各个平台上保持一致。给定的非 ASCII 字符是否被认为属于这些类取决于用于正则表达式函数或操作符的排序，或默认情况下取决于数据库的 LC_CTYPE 区域设置。非 ASCII 字符的分类在不同名称的区域设置中甚至在不同平台上也可能有所不同。（但 C 区域设置从不认为任何非 ASCII 字符属于这些类。）除了这些标准字符类之外，UXsinoDB 定义了 word 字符类，它与 alnum 加下划线（_）字符相同，以及 ascii 字符类，其中包含完全 7 位 ASCII 集。

有两种特殊情况的括号表达式：[[:<:]] 和 [[:>:]] 是约束条件，分别在单词的开头和结尾匹配空字符串。单词定义为不以单词字符为前导或后继的单词字符序列。单词字符是属于 word 字符类的任何字符，即任何字母、数字或下划线。这是一种扩展，与但不指定为 POSIX 1003.2 兼容，并且应谨慎在旨在可移植到其他系统的软件中使用。下面描述的约束条件转义通常更可取；它们不更标准，但更容易输入。

8.3.3. 正则表达式转义

转义是以 \ 开头，后跟字母数字字符的特殊序列。转义有几种类型：字符输入，类简写，约束条件转义和反向引用。\ 后跟字母数字字符但不构成有效转义的是 ARE 中不合法的。在 ERE 中，没有转义：在方括号表达式之外，\ 后跟字母数字字符仅表示该字符作为普通字符，而在方括号表达式之内，\ 是普通字符。（这是 ERE 和 ARE 之间唯一的实际不兼容性。）

字符转义用于更轻松地在正则表达式中指定非打印和其他不方便的字符。它们在表 正则表达式字符转义中列出。

类别缩写转义提供了一些常用字符类别的缩写。它们在表 正则表达式类别缩写转义中列出。

约束转义是一个约束，如果满足特定条件，则匹配空字符串，写成一个转义符。它们在表 正则表达式约束转义中列出。

反向引用（\n）匹配与前一个括号子表达式匹配的相同字符串，该括号子表达式由数字 n 指定（请参见表 正则表达式反向引用）。例如，([bc])\1 匹配 bb 或 cc，但不匹配 bc 或 cb。子表达式必须完全在反向引用之前出现在 RE 中。子表达式按其前导括号的顺序编号。非捕获括号不定义子表达式。反向引用仅考虑由引用的子表达式匹配的字符串字符，而不考虑其中包含的任何约束。例如，(^\d)\1 将匹配 22。

表 正则表达式字符转义

十六进制数字为 0-9，a-f，A-F。八进制数字为 0-7。

指定 ASCII 范围（0-127）之外的数值的数字字符转义在数据库编码上有不同的含义。当编码为 UTF-8 时，转义值等同于 Unicode 代码点，例如 \u1234 表示字符 U+1234。对于其他多字节编码，字符转义通常只指定字符的字节值的串联。如果转义值在数据库编码中没有对应的合法字符，则不会引发错误，但它永远不会匹配任何数据。

字符转义始终被视为普通字符。例如，\135 在 ASCII 中表示 ]，但 \135 不会终止括号表达式。

表 正则表达式类别缩写转义

类别速记转义也适用于括号表达式，尽管上面显示的定义在该上下文中不完全符合语法规则。例如，[a-c\d] 等价于 [a-c[:digit:]]。

表 正则表达式约束转义符

单词的定义如 [[:<:]] 和 [[:>:]] 规范中所述。约束转义符不能在括号表达式中使用。

表 正则表达式反向引用

注意
八进制字符输入转义符和反向引用之间存在固有的歧义，可以通过以下启发式方法解决，如上所示。前导零始终表示八进制转义符。单个非零数字，后面没有其他数字，始终被视为反向引用。不以零开头的多位数字序列，如果在适当的子表达式之后出现（即数字在反向引用的合法范围内），则被视为反向引用，否则被视为八进制转义符。

8.3.4. 正则表达式元语法

除了上面描述的主要语法之外，还有一些特殊形式和杂项语法设施可用。

正则表达式可以以两个特殊的指示符前缀之一开头。如果正则表达式以 ***: 开头，则其余部分被视为 ARE。（在 UXsinoDB 中，这通常没有影响，因为假定正则表达式是 ARE。）但是如果在正则表达式函数的 flags 参数中指定了 ERE 或 BRE 模式，则会产生影响。如果一个 RE 以 ***= 开头，则 RE 的其余部分被视为一个字面字符串，其中所有字符都被视为普通字符。

一个 ARE 可以以嵌入选项开头：一个序列 (?xyz)（其中 xyz 是一个或多个字母字符）指定影响 RE 其余部分的选项。这些选项覆盖了先前确定的任何选项，特别是它们可以覆盖由正则表达式操作符暗示的大小写敏感行为，或者正则表达式函数的 flags 参数。可用的选项字母在表 ARE 嵌入选项字母中显示。请注意，这些相同的选项字母用于正则表达式函数的 flags 参数。

表 ARE 嵌入选项字母

嵌入选项在终止序列的 ) 处生效。它们只能出现在 ARE 的开头（在任何 ***: 指示符之后，如果有的话）。

除了通常的 RE 语法，其中所有字符都是重要的，还有一种扩展的语法，可以通过指定嵌入的 x 选项来使用。在扩展语法中，RE 中的空格字符被忽略，所有在 # 和下一个换行符之间的字符也被忽略。以下换行符（或 RE 的结尾）允许对复杂的 RE 进行分段和注释。基本规则有三个例外：

• 以反斜杠 \ 为前缀的空格字符或 # 会被保留。
• 方括号表达式中的空格或 # 会被保留。
• 多字符符号（如 (?:）中不能出现空格和注释。

为此，空格字符包括空格、制表符、换行符和属于 space 字符类的任何字符。

最后，在 ARE 中，除了方括号表达式外，序列 (?#ttt)（其中 ttt 是不包含 ) 的任何文本）是一个注释，完全被忽略。同样，这在多字符符号（如 (?:）的字符之间是不允许的。这些注释更多地是历史遗留物而不是有用的设施，它们的使用已被弃用；请改用扩展语法。

如果初始的 ***= 指示符指定将用户的输入视为字面字符串而不是 RE，则这些元语法扩展都不可用。

8.3.5. 正则表达式匹配规则

如果一个 RE 可以匹配给定字符串的多个子字符串，则 RE 匹配最早出现在字符串中的子字符串。如果 RE 可以匹配从该点开始的多个子字符串，则取最长可能的匹配或最短可能的匹配，具体取决于 RE 是贪婪的还是非贪婪的。

RE 是贪婪还是非贪婪由以下规则确定：

• 大多数原子和所有约束都没有贪婪属性（因为它们无法匹配可变数量的文本）。
• 在 RE 周围添加括号不会改变其贪婪性。
• 具有固定重复量子化器（{m} 或 {m}?）的量化原子具有与原子本身相同的贪婪性（可能没有）。
• 具有其他正常量化器的量化原子（包括 {m,n}，其中 m 等于 n）是贪婪的（偏爱最长匹配）。
• 具有非贪婪量化器的量化原子（包括 {m,n}?，其中 m 等于 n）是非贪婪的（偏爱最短匹配）。
• 分支——即没有顶层 | 操作符的 RE——具有与其中第一个具有贪婪属性的量化原子相同的贪婪性。
• 由两个或更多分支连接的 RE，使用 | 操作符总是贪婪的。

以上规则不仅将贪婪属性与单个量化原子相关联，还将贪婪属性与包含量化原子的分支和整个 RE 相关联。这意味着匹配是以使分支或整个 RE 匹配最长或最短的子字符串作为整体的方式进行的。一旦确定了整个匹配的长度，匹配任何特定子表达式的部分是基于该子表达式的贪婪属性确定的，其中在 RE 中较早开始的子表达式优先于后面开始的子表达式。

以下是这意味着什么的示例：

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
Result: 123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
Result: 1

在第一种情况下，RE 作为整体是贪婪的，因为 Y* 是贪婪的。它可以从 Y 开始匹配，并匹配从那里开始的最长可能字符串，即 Y123。输出是其中括号部分，即 123。在第二种情况下，RE 作为整体是非贪婪的，因为 Y*? 是非贪婪的。它可以从 Y 开始匹配，并匹配从那里开始的最短可能字符串，即 Y1。子表达式 [0-9]{1,3} 是贪婪的，但它不能改变关于整个匹配长度的决定；因此，它被迫只匹配 1。

简而言之，当一个 RE 包含贪婪和非贪婪子表达式时，总匹配长度要么尽可能长，要么尽可能短，具体取决于分配给整个 RE 的属性。分配给子表达式的属性仅影响它们相对于彼此允许“吃掉”的匹配量。

量词符 {1,1} 和 {1,1}? 可用于分别强制子表达式或整个 RE 的贪婪或非贪婪。当您需要整个 RE 具有与其元素推导出的贪婪属性不同的贪婪属性时，这非常有用。例如，假设我们正在尝试将包含一些数字的字符串分离为数字和它们之前和之后的部分。我们可以尝试像这样做：

SELECT regexp_match('abc01234xyz', '(.*)(\d+)(.*)');
Result: {abc0123,4,xyz}

这没用：第一个 .* 是贪婪的，因此它尽可能多地“吃掉”，留下 \d+ 在最后一个可能的位置匹配，即最后一个数字。我们可以尝试通过使其非贪婪来修复它：

SELECT regexp_match('abc01234xyz', '(.*?)(\d+)(.*)');
Result: {abc,0,""}

这也不起作用，因为现在整个 RE 都是非贪婪的，因此它尽可能快地结束整个匹配。我们可以通过强制整个 RE 为贪婪来获得我们想要的结果：

SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
Result: {abc,01234,xyz}

单独控制 RE 的整体贪婪性和其组件的贪婪性允许在处理可变长度模式时具有很大的灵活性。

在决定什么是更长或更短的匹配时，匹配长度是以字符为单位而不是排序元素来衡量的。空字符串被认为比没有匹配更长。例如：bb* 匹配 abbbc 的三个中间字符；(week|wee)(night|knights) 匹配 weeknights 的所有十个字符；当 (.*).* 与 abc 匹配时，括号内的子表达式匹配所有三个字符；当 (a*)* 与 bc 匹配时，整个 RE 和括号内的子表达式都匹配一个空字符串。

如果指定了不区分大小写的匹配，则其效果就像字母表中所有大小写区分都消失了一样。当一个存在于多个大小写中的字母作为普通字符出现在方括号表达式之外时，它实际上被转换为包含两个大小写的方括号表达式，例如，x 变成了 [xX]。当它出现在方括号表达式中时，它的所有大小写对应项都被添加到方括号表达式中，例如，[x] 变成了 [xX]，[^x] 变成了 [^xX]。

如果指定了对换行符敏感的匹配，则 . 和使用 ^ 的方括号表达式将永远不会匹配换行符（因此匹配不会跨越行，除非 RE 显式包括换行符），并且 ^ 和 $ 将在换行符之后和之前匹配一个空字符串，除了在字符串的开头和结尾匹配。但是，ARE 转义 \A 和 \Z 仍然只匹配字符串的开头或结尾。此外，字符类简写 \D 和 \W 将匹配换行符，而不管此模式如何。（在 UXsinoDB 之前版本中，它们在对换行符敏感的模式下不匹配换行符。写 [^[:digit:]] 或 [^[:word:]] 以获得旧行为。）

如果指定了部分对换行符敏感的匹配，则与对换行符敏感的匹配一样，这会影响 . 和方括号表达式，但不会影响 ^ 和 $。

如果指定了反向部分对换行符敏感的匹配，则与对换行符敏感的匹配一样，这会影响 ^ 和 $，但不会影响 . 和方括号表达式。这不是非常有用，但提供对称性。

8.3.6. 限制和兼容性

在此实现中，没有对 RE 的长度施加特定的限制。然而，旨在高度可移植的程序不应使用超过 256 字节的 RE，因为符合 POSIX 标准的实现可能会拒绝接受这样的 RE。

ARE 与 POSIX ERE 实际上不兼容的唯一特性是，它在括号表达式中不失其特殊意义。所有其他 ARE 特性使用的语法在 POSIX ERE 中是非法的，或者具有未定义或未指定的效果；同样，BRE 和 ERE 的控制器的 *** 语法也在 POSIX 语法之外。

许多 ARE 扩展是从 Perl 借来的，但是一些已经被更改以清除它们，还有一些 Perl 扩展不存在。值得注意的不兼容性包括 \b、\B、缺乏对尾部换行的特殊处理，对受换行敏感匹配影响的内容添加补括号表达式，对前向/后向约束中的括号和后向引用的限制，以及最长/最短匹配（而不是首次匹配）匹配语义。

8.3.7. 基本正则表达式

BRE 与 ERE 在几个方面不同。在 BRE 中，|、+ 和 ? 是普通字符，没有相应的功能。边界的分隔符是 \{ 和 \}，{ 和 } 本身是普通字符。用于嵌套子表达式的括号是 \( 和 \)，( 和 ) 本身是普通字符。^ 是普通字符，除非在 RE 的开头或括号化的子表达式的开头，$ 是普通字符，除非在 RE 的结尾或括号化的子表达式的结尾，如果 * 出现在 RE 的开头或括号化的子表达式的开头（在可能的前导 ^ 之后），则是普通字符。最后，可以使用单个数字反向引用，\< 和 \> 是 [[:<:]] 和 [[:>:]] 的同义词；在 BRE 中不提供其他转义。

8.3.8. 与 XQuery 的区别 (LIKE_REGEX)

自 SQL:2008 以来，SQL 标准包括正则表达式操作符和函数，根据 XQuery 正则表达式标准执行模式匹配：

• LIKE_REGEX
• OCCURRENCES_REGEX
• POSITION_REGEX
• SUBSTRING_REGEX
• TRANSLATE_REGEX

UXsinoDB 目前不实现这些操作符和函数。您可以在表 正则表达式函数对应关系中显示的每种情况下获得近似等效的功能。（在此表中，双方的各种可选子句已被省略。）

表 正则表达式函数对应关系

类似于 UXsinoDB 提供的正则表达式函数也可在许多其他 SQL 实现中使用，而 SQL 标准函数则没有被广泛实现。每个实现中正则表达式语法的一些细节可能会有所不同。

SQL 标准操作符和函数使用 XQuery 正则表达式，这与上面描述的 ARE 语法非常接近。现有基于 POSIX 的正则表达式功能与 XQuery 正则表达式之间的显着差异包括：

• 不支持 XQuery 字符类减法。使用以下内容仅匹配英语辅音字母的示例： [a-z-[aeiou]]。
• 不支持 XQuery 字符类速记符 \c、\C、\i 和 \I。
• 不支持使用 \p{UnicodeProperty} 或反向 \P{UnicodeProperty} 的 XQuery 字符类元素。
• POSIX 根据当前语言环境（可以通过将 COLLATE 子句附加到操作符或函数来控制）解释字符类，例如 \w（请参见表 正则表达式类别缩写转义）。XQuery 通过引用 Unicode 字符属性来指定这些类，因此只有在使用相同的语言环境时才能获得等效的行为。遵循 Unicode 规则的语言环境。
• SQL 标准（不是 XQuery 本身）试图为比 POSIX 更多的“换行符”变体提供服务。上面描述的敏感换行匹配选项仅将 ASCII NL（\n）视为换行符，但是 SQL 要求我们将 CR（\r）、CRLF（\r\n）（Windows 风格的换行符）以及一些仅限于 Unicode 的字符（如 LINE SEPARATOR（U+2028））也视为换行符。值得注意的是，根据 SQL，. 和 \s 应将 \r\n 视为一个字符而不是两个字符。
• 在表 正则表达式字符转义中描述的字符输入转义中，XQuery 仅支持 \n、\r 和 \t。
• XQuery 不支持方括号表达式中的字符类的 [:name:] 语法。
• XQuery 没有前瞻或后顾约束，也没有在表 正则表达式约束转义中描述的任何约束转义。
• 在正则表达式元语法中描述的元语法形式在 XQuery 中不存在。
• XQuery 定义的正则表达式标志字母与 POSIX 的选项字母相关但不相同表 ARE 嵌入选项字母。虽然 i 和 q 选项的行为相同，但其他选项则不同：
  • XQuery 的 s（允许点匹配换行符）和 m（允许 ^ 和 $ 在换行符处匹配）标志提供了与 POSIX 的 n、p 和 w 标志相同的行为，但它们与 POSIX 的 s 和 m 标志的行为不匹配。特别要注意的是，在 POSIX 中，点匹配换行符是默认行为，但在 XQuery 中不是。
  • XQuery 的 x（忽略模式中的空格）标志与 POSIX 的扩展模式标志明显不同。POSIX 的 x 标志还允许 # 在模式中开始注释，并且 POSIX 不会忽略反斜杠后的空格字符。

8.4. REGEXP 和 RLIKE 正则表达式

string  REGEXP      pattern
string  NOT REGEXP  pattern
string  RLIKE       pattern
string  NOT RLIKE   pattern

如果该 string 匹配了提供的 pattern，那么 REGEXP（或 RLIKE）表达式返回真（和预期的一样，如果 REGEXP（或 RLIKE）返回真，那么 NOT REGEXP（或 RLIKE）表达式返回假，反之亦然。一个等效的表达式是 NOT (string REGEXP(或 RLIKE) pattern)。

如果 pattern 不包含百分号或者下划线，那么该模式只代表它本身的串；这时候 REGEXP 的行为就象等号操作符。在 pattern 里的下划线（_）代表（匹配）任何单个字符；而一个百分号（%）匹配任何零或更多个字符的序列。

示例如下所示。

'Michael!' REGEXP '.*'                       true
'new*\n*line' REGEXP 'new\\*.\\*line'         false
'a' REGEXP '^[a-d]'                          true

注意
string 和 pattern 为空串时进行 regexp，mysql 模式下会报错，结果如下所示。

uxdb=# select 1 regexp '';
ERROR:  Got error 'empty (sub)expression' from pattern

9. 数据类型格式化函数

UXsinoDB 格式化函数提供了一组强大的工具，用于将各种数据类型（日期/时间、整数、浮点数、数值）转换为格式化字符串，并将格式化字符串转换为特定的数据类型。表 格式化函数列出了这些函数。这些函数都遵循一个通用的调用约定：第一个参数是要格式化的值，第二个参数是定义输出或输入格式的模板。

表 格式化函数

提示
to_timestamp 和 to_date 存在的目的是处理无法通过简单转换进行转换的输入格式。对于大多数标准日期/时间格式，只需将源字符串强制转换为所需的数据类型即可，这样做更容易。同样，对于标准数字表示，to_number 是不必要的。

在 to_char 输出模板字符串中，有一些模式会被识别并根据给定的值替换为适当格式的数据。任何不是模板模式的文本都会被简单地复制。同样，在输入模板字符串（用于其他函数）中，模板模式标识要由输入数据字符串提供的值。如果模板字符串中有字符不是模板模式，则输入数据字符串中相应的字符会被简单地跳过（无论它们是否等于模板字符串字符）。

表 日期/时间格式化的模板模式显示了可用于格式化日期和时间值的模板模式。

表 日期/时间格式化的模板模式

可以对任何模板模式应用修饰符以改变其行为。例如，FMMonth 是带有 FM 修饰符的 Month 模式。表 日期/时间格式化的模板模式修饰符显示了日期/时间格式化的修饰符模式。

表 日期/时间格式化的模板模式修饰符

日期/时间格式化的使用说明：

• FM 抑制前导零和尾随空格，否则会添加以使模式的输出成为固定宽度。在 UXsinoDB 中，FM 仅修改下一个规范，而在 Oracle 中，FM 影响所有后续规范，并且重复的 FM 修饰符切换填充模式的开关。

• TM 抑制尾随空格，无论是否指定了 FM。

• to_timestamp 和 to_date 忽略输入中的字母大小写；因此，例如 MON、Mon 和 mon 都接受相同的字符串。使用 TM 修饰符时，大小写折叠按照函数输入排序规则执行。

• to_timestamp 和 to_date 跳过输入字符串开头和日期时间值周围的多个空格，除非使用了 FX 选项。例如，to_timestamp(' 2000 JUN', 'YYYY MON') 和 to_timestamp('2000 - JUN', 'YYYY-MON') 可行，但 to_timestamp('2000 JUN', 'FXYYYY MON') 返回错误，因为 to_timestamp 仅期望单个空格。必须将 FX 指定为模板中的第一项。

• to_timestamp 和 to_date 中模板字符串中的分隔符（空格或非字母/非数字字符）匹配输入字符串中的任何单个分隔符或跳过，除非使用 FX 选项。例如，to_timestamp('2000JUN', 'YYYY///MON') 和 to_timestamp('2000/JUN', 'YYYY MON') 可行，但 to_timestamp('2000//JUN', 'YYYY/MON') 返回错误，因为输入字符串中的分隔符数量超过模板中的分隔符数量。

  如果指定了 FX，则模板字符串中的分隔符在输入字符串中精确匹配一个字符。但请注意，输入字符串字符不必与模板字符串中的分隔符相同。例如，to_timestamp('2000/JUN','FXYYYY MON') 可行，但 to_timestamp('2000/JUN', 'FXYYYY MON') 返回错误，因为模板字符串中的第二个空格消耗了输入字符串中的第二个空格。

• to_timestamp_tz 和 to_timestamp 的功能相同，输出的时区信息为默认的会话时区，时间为输出的时间转化为会话时区的时间，暂不支持 NLS_TIMESTAMP_TZ_FORMAT 参数来控制输出，在 uxdb 标准模式下不支持带时区（'20031213181318-8:00'）或不带时区（'20031213181318'）日期时间格式字符串输出，兼容模式下可以正常输出不带时区的字符串，如：'20031213181318'，当参数一与参数二的时间格式字符不完全匹配时，如参数一缺少日的格式，输出时会自动补 1（年月日）和补 0（时分秒），会输出错误结果，不建议此类用法，如下所示。

  uxdb=# select to_timestamp_tz('2022-10-13 17:13:18','YYYY-MM HH24:MI:SS'); 
  to_timestamp_tz 
  ------------------------ 
  2022-10-01 13:17:13-07 
  (1 row) 
  

• TZH 模板模式可以匹配带符号的数字。如果没有 FX 选项，减号可能会有歧义，并且可能会被解释为分隔符。解决这种歧义的方法如下：如果模板字符串中 TZH 前的分隔符数量小于输入字符串中减号前的分隔符数量，则减号被解释为 TZH 的一部分。否则，减号被视为值之间的分隔符。例如，to_timestamp('2000 -10', 'YYYY TZH') 将 -10 匹配到 TZH，但是 to_timestamp('2000 -10', 'YYYY TZH') 将 10 匹配到 TZH。

• to_char 模板中允许普通文本，并且会直接输出。您可以将子字符串放在双引号中，以强制将其解释为文字文本，即使它包含模板模式。例如，在 '"Hello Year "YYYY' 中，YYYY 将被年份数据替换，但是 Year 中的单个 Y 不会被替换。在 to_date、to_number 和 to_timestamp 中，文字文本和双引号字符串会导致跳过包含在字符串中的字符数；例如 "XX" 跳过两个输入字符（无论它们是否为 XX）。

  提示
  在 UXsinoDB 之前版本中，可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如，to_timestamp('2000y6m1d','yyyy-MM-DD') 曾经可以工作。现在，您只能使用字母字符来实现这个目的。例如，to_timestamp('2000y6m1d','yyyytMMtDDt') 和 to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') 跳过 y、m 和 d。

• 如果要在输出中包含双引号，必须在其前面加上反斜杠，例如 '\"YYYYMonth\"'。反斜杠在双引号字符串之外不起作用。在双引号字符串中，反斜杠会导致下一个字符被直接取出，无论它是什么（但是，除非下一个字符是双引号或另一个反斜杠，否则这没有特殊效果）。

• 在 to_timestamp 和 to_date 中，如果年份格式规范少于四位数字，例如 YYY，并且提供的年份少于四位数字，则年份将调整为最接近 2020 年的年份，例如 95 变成 1995。

• 在 to_timestamp 和 to_date 中，负数年份被视为公元前。如果同时写入负数年份和显式的 BC 字段，则再次得到公元后。年份为零的输入被视为公元前 1 年。

• 在 to_timestamp 和 to_date 中，YYYY 转换在处理超过 4 位数字的年份时有限制。您必须在 YYYY 后使用一些非数字字符或模板，否则年份始终被解释为 4 位数字。例如（使用年份 20000）：to_date('200001131','YYYYMMDD') 将被解释为一个 4 位数的年份；相反，使用年份后的非数字分隔符，如 to_date('20000-1131','YYYY-MMDD') 或 to_date('20000Nov31', 'YYYYMonDD')。

• 在 to_timestamp 和 to_date 中，如果有 YYY、YYYY 或 Y,YYY 字段，则接受但忽略 CC（世纪）字段。如果将 CC 与 YY 或 Y 一起使用，则结果将计算为指定世纪的那一年。如果指定了世纪但没有年份，则假定为该世纪的第一年。

• 在 to_timestamp 和 to_date 中，接受工作日名称或数字（DAY、D 和相关字段类型），但在计算结果时将其忽略。季度（Q）字段也是如此。

• 在 to_timestamp 和 to_date 中，可以用两种方式指定 ISO 8601 周编号日期（与公历日期不同）：

  1. 年份、周数和工作日：例如，to_date('2006-42-4', 'IYYY-IW-ID') 返回日期 2006-10-19。如果省略工作日，则假定为 1（星期一）。
  2. 年份和年内日数：例如，to_date('2006-291','IYYY-IDDD') 也返回 2006-10-19。

  尝试使用 ISO 8601 周编号字段和公历日期字段的混合输入日期是不合理的，会导致错误。ISO 8601 周编号年的上下文中，“月”或“月中日”这样的概念是没有意义的。在公历年的上下文中，ISO 周没有意义。

  注意
  虽然 to_date 会拒绝混合使用公历和 ISO 周编号日期字段，但 to_char 不会，因为输出格式规范（如 YYYY-MM-DD (IYYY-IDDD)）可能很有用。但是要避免编写类似于 IYYY-MM-DD 的内容；这会在年初产生令人惊讶的结果。（有关更多信息，请参见 EXTRACT, date_part。）

• 在 to_timestamp 中，毫秒（MS）或微秒（US）字段用作小数点后的秒数位。例如，to_timestamp('12.3','SS.MS') 不是 3 毫秒，而是 300，因为转换将其视为 12 + 0.3 秒。因此，对于格式 SS.MS，输入值 12.3、12.30 和 12.300 指定了相同数量的毫秒。要获得三毫秒，必须编写 12.003，转换将其视为 12 + 0.003 = 12.003 秒。以下是一个更复杂的示例：to_timestamp('15:12:02.020.001230','HH24:MI:SS.MS.US') 是 15 小时 12 分钟 2 秒 + 20 毫秒 + 0.001230 秒 1230 微秒 = 2.021230 秒。

• to_char(...,'ID') 的星期几编号与 extract(isodow from...) 函数相匹配，但 to_char(...,'D') 的星期几编号与 extract(dow from...) 函数不匹配。

• to_char(interval) 将 HH 和 HH12 格式化为 12 小时制，例如零小时和 36 小时都输出为 12，而 HH24 输出完整的小时值，可以超过 interval 值中的 23。

表 数字格式化的模板模式显示了可用于格式化数字值的模板模式。

表 数字格式化的模板模式

数字格式化的使用说明：

• 0 指定将始终打印的数字位置，即使它包含前导/尾随零。9 也指定数字位置，但如果它是前导零，则将其替换为空格，而如果它是尾随零并且指定了填充模式，则将其删除。（对于 to_number()，这两个模式字符是等效的。）

• 模式字符 S、L、D 和 G 表示当前区域设置定义的符号、货币符号、小数点和千位分隔符字符。和 lc_numeric 一起使用。模式字符 period 和 comma 表示这些确切的字符，具有小数点和千位分隔符的含义，不考虑语言环境。

• 如果在 to_char() 的模式中没有明确指定符号，一个列将被保留用于符号，并且它将被锚定到（出现在）数字的左侧。如果 S 出现在某些 9 的左侧，它也将被锚定到数字上。

• 使用 SG、PL 或 MI 格式化的符号不会锚定到数字上；例如，to_char(-12, 'MI9999') 会产生 '- 12'，但 to_char(-12, 'S9999') 会产生 ' -12'。（Oracle 实现不允许在 9 之前使用 MI，而是要求 9 在 MI 之前。）

• TH 不会转换小于零的值，也不会转换小数。

• PL、SG 和 TH 是 UXsinoDB 的扩展。

• 在 to_number 中，如果使用了非数据模板模式，如 L 或 TH，则相应数量的输入字符将被跳过，无论它们是否匹配模板模式，除非它们是数据字符（即数字、符号、小数点或逗号）。例如，TH 将跳过两个非数据字符。

• V 与 to_char 一起，将输入值乘以 10^n，其中 n 是 V 后面的数字的数量。使用 to_number 的 V 以类似的方式进行除法运算。to_char 和 to_number 不支持将 V 与小数点组合使用（例如，不允许使用 99.9V99）。

• EEEE（科学计数法）不能与除数字和小数点模式之外的任何其他格式化模式或修饰符组合使用，并且必须位于格式字符串的末尾（例如，9.99EEEE 是一个有效的模式）。

可以对任何模板模式应用某些修饰符以改变其行为。例如，FM99.99 是带有 FM 修饰符的 99.99 模式。

表 数字格式化的模板模式修饰符显示了数字格式化的修饰符模式。

表 数字格式化的模板模式修饰符

表 to_char 示例显示了一些使用 to_char 函数的示例。

表 to_char 示例

10. 日期/时间函数和操作符

表 日期/时间函数显示了可用于日期/时间值处理的函数，详细信息在以下子节中。表 日期/时间操作符说明了基本算术操作符（+、* 等）的行为。有关格式化函数，请参见数据类型格式化函数。

此外，表 比较操作符中显示的常规比较操作符也适用于日期/时间类型。日期和时间戳（带或不带时区）都是可比较的，而时间（带或不带时区）和间隔只能与相同数据类型的其他值进行比较。当将不带时区的时间戳与带时区的时间戳进行比较时，前者的值被假定为给定的时区。TimeZone 配置参数被假定为本地时间，并被转换为 UTC 以与 UTC 内部值进行比较。同样，当比较日期值与时间戳时，日期值被假定为当天的午夜在 TimeZone 时区。下面所描述的所有函数和操作符都有两个变体：一个接受带有时区的 time 或 timestamp 输入，另一个接受不带时区的 time 或 timestamp 输入。为了简洁起见，这些变体没有分别显示。此外，+ 和 * 操作符成对出现（例如，date + integer 和 integer + date）；我们只显示这样一对中的一个。

表 日期/时间操作符

表 日期/时间函数

表 weekdate 函数 mode 参数说明

除了这些函数外，还支持 SQL 的 OVERLAPS 操作符：

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

当两个时间段（由它们的端点定义）重叠时，此表达式返回 true，否则返回 false。端点可以指定为日期、时间或时间戳对；或者作为日期、时间或时间戳后跟一个间隔。当提供一对值时，可以先写入开始或结束；OVERLAPS 自动将该对的较早值作为开始。每个时间段被认为表示半开区间 start <= time < end，除非 start 和 end 相等，在这种情况下它表示单个时间点。这意味着例如，只有一个端点相同的两个时间段不重叠。

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: true

当向具有时区值的时间戳添加（或从中减去）一个时间间隔时，天组件将 timestamp with time zone 提前或减去指定的天数，使一天的时间保持不变。在夏时制变化时（当会话时区设置为识别夏令时的时区时），这意味着间隔“1 天”不一定等于间隔“24 小时”。例如，会话时区设置为 America/Denver：

SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
Result: 2005-04-03 12:00:00-06
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
Result: 2005-04-03 13:00:00-06

这是因为由于时区“America/Denver”中的夏令时更改，在 2005-04-03 02:00:00 时跳过了一个小时。

请注意，由于不同的月份具有不同数量的天数，因此“age”函数返回的“months”字段可能存在歧义。UXsinoDB 的方法在计算部分月份时使用两个日期中较早的月份。例如，“age('2004-06-01'，'2004-04-30')”使用 4 月份得出“1 mon 1 day”，而使用 5 月份则会得出“1 mon 2 days”，因为 5 月份有 31 天，而 4 月份只有 30 天。

日期和时间戳的减法也可以很复杂。一个概念上简单的方法是使用 EXTRACT(EPOCH FROM...) 将每个值转换为秒数，然后减去结果；这将调整每个月的天数、时区更改和夏令时调整。使用 - 操作符减去日期或时间戳值返回值之间的天数（24 小时）、小时/分钟/秒数，进行相同的调整。函数 age 返回年、月、日和小时/分钟/秒数，执行逐字段减法，然后调整负字段值。以下查询说明了这些方法的差异。样本结果是使用 timezone='US/Eastern' 生成的；在使用的两个日期之间有一个夏令时更改：

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
        / 60 / 60 / 24;
Result: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Result: 4 mons

10.1. EXTRACT，date_part

EXTRACT(field FROM source)

extract 函数从日期/时间值中检索子字段，例如年份或小时数。source 必须是类型为 timestamp、time 或 interval 的值表达式。（类型为 date 的表达式会被转换为 timestamp，因此也可以使用。）field 是一个标识符或字符串，用于选择从源值中提取的字段。extract 函数返回类型为 numeric 的值。以下是有效的字段名称：

• century 世纪

  SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
  Result: 20
  SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 21
  

  第一个世纪始于公元 0001-01-01 00:00:00，尽管当时人们并不知道。这个定义适用于所有格里高利历国家。没有世纪编号 0，你从 -1 世纪到 1 世纪。

• day 对于 timestamp 值，表示日期（1-31）；对于 interval 值，表示天数

  SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 16
  
  SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
  Result: 40
  

• decade 年份除以 10 的结果

  SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 200
  

• dow 星期几，从星期日（0）到星期六（6）

  SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 5
  

  请注意，extract 的星期几编号与 to_char(..., 'D') 函数的编号不同。

• doy 一年中的第几天（1-365/366）

  SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 47
  

• epoch 对于带有时区的 timestamp 值，表示自 1970-01-01 00:00:00 UTC 以来的秒数（时间戳早于此时间的秒数为负数）；对于 date 和 timestamp 值，以 1970-01-01 00:00:00 为基准的秒数，不考虑时区或夏令时规则；对于 interval 值，是时间间隔中的总秒数。

  SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
  Result: 982384720.12
  
  SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
  Result: 982355920.12
  
  SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
  Result: 442800
  

  您可以使用 to_timestamp 将时代值转换回 timestamp with time zone：

  SELECT to_timestamp(982384720.12);
  Result: 2001-02-17 04:38:40.12+00
  

  请注意，将 to_timestamp 应用于从 date 或 timestamp 值中提取的时代可能会产生误导性的结果：结果将有效地假定原始值已在 UTC 中给出，这可能不是实际情况。

• hour 小时字段（0-23）

  SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 20
  

• isodow 星期几，从星期一（1）到星期日（7）

  SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
  Result: 7
  

  这与 dow 相同，除了星期日。这与 ISO 8601 星期几编号相匹配。

• isoyear 日期所在的 ISO 8601 周编号年份（不适用于时间间隔）

  SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
  Result: 2005
  SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
  Result: 2006
  

  每个 ISO 8601 周编号年从包含 1 月 4 日的星期一开始，因此在 1 月初或 12 月末，ISO 年可能与公历年不同。

• julian 与日期或时间戳相对应的儒略日（不适用于时间间隔）。不是当地午夜的时间戳会导致小数值。

  SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
  Result: 2453737
  SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
  Result: 2453737.50000000000000000000
  

• microseconds 秒数字段，包括小数部分，乘以 1,000,000；注意这包括整秒。

  SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
  Result: 28500000
  

• millennium 千年

  SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 3
  

  1900 年代的年份属于第二个千年。第三个千年从 2001 年 1 月 1 日开始。

• milliseconds 秒数字段，包括小数部分，乘以 1,000；注意这包括整秒。

  SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
  Result: 28500
  

• minute 分钟字段（0-59）

  SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 38
  

• month 对于时间戳值，表示一年中的月份（1-12）；对于间隔值，表示月数模 12（0-11）

  SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 2
  
  SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
  Result: 3
  
  SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
  Result: 1
  

• quarter 一年中的季度（1-4）

  SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 1
  

• second 秒数字段，包括任何小数秒。

  SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 40
  
  SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
  Result: 28.5
  

• timezone 与 UTC 的时区偏移，以秒为单位。正值对应于 UTC 东部的时区，负值对应于 UTC 西部的时区。（严格来说，UXsinoDB 不使用 UTC，因为不处理闰秒。）

• timezone_hour 时区偏移的小时部分。

• timezone_minute 时区偏移的分钟部分。

• week ISO 8601 周编号年的周数。根据定义，ISO 周从星期一开始，一年的第一周包含该年的 1 月 4 日。换句话说，一年的第一个星期四在该年的第一周。

  在 ISO 周编号系统中，1 月初的日期可能属于上一年的第 52 或第 53 周，而 12 月底的日期可能属于下一年的第一周。例如，2005 年 1 月 1 日属于 2004 年的第 53 周，2006 年 1 月 1 日属于 2005 年的第 52 周，而 2012 年 12 月 31 日属于 2013 年的第一周。建议使用 isoyear 字段和 week 字段一起使用以获得一致的结果。

  SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 7
  

• year 年份字段。请记住，没有“公元前 0 年”，因此应谨慎地从“公元后”年份中减去“公元前”年份。

  SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
  Result: 2001
  

  注意
  当输入值为 +/-Infinity 时，extract 对于单调递增字段（epoch、julian、year、isoyear、decade、century 和 millennium）返回 +/-Infinity。对于其他字段，返回 NULL。

extract 函数主要用于计算处理。有关格式化日期/时间值以供显示的信息，请参见数据类型格式化函数。

date_part 函数是基于传统的 Ingres 等效函数 extract 的模型：

date_part('field', source)

请注意，这里的 field 参数需要是字符串值，而不是名称。对于 date_part，有效的字段名称与 extract 相同。由于历史原因，date_part 函数返回 double precision 类型的值。在某些用途中，这可能会导致精度损失。建议改用 extract。

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

10.2. date_trunc

函数 date_trunc 在概念上类似于数字的 trunc 函数。

date_trunc(field, source [, time_zone ])

source 是 timestamp、timestamp with time zone 或者 interval 表达式。（类型 date 和 time 的值分别自动转换为 timestamp 或 interval。）field 选择截断输入值的精度。返回值类似于 timestamp、timestamp with time zone 或者 interval 的类型，并且它包含所有比选定的字段设置为零（或者日和月的一个字段）更小的字段。

field 的有效值为：

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

当输入值为类型为 timestamp with time zone 时，截断是相对于特定时区进行的；例如，截断到 day 会产生一个在该时区中午夜的值。默认情况下，截断是相对于当前的 TimeZone 设置进行的，但是可以提供可选的 time_zone 参数来指定不同的时区。

在处理 timestamp without time zone 或 interval 输入时不能指定时区。这些始终按面值处理。

示例（假设本地时区为 America/New_York）：

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05

SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

10.3. date_bin

函数 date_bin 将输入的时间戳“分组”到指定的间隔（步幅），并与指定的起点对齐。

date_bin(stride, source, origin)

source 是类型为 timestamp 或 timestamp with time zone 的值表达式。（类型为 date 的值会自动转换为 timestamp。）stride 是类型为 interval 的值表达式。返回值同样是类型为 timestamp 或 timestamp with time zone，并标记了 source 所在的分组的开头。

示例：

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30

在完整单位（1 分钟，1 小时等）的情况下，它给出与类似的 date_trunc 调用相同的结果，但不同之处在于 date_bin 可以截断到任意间隔。

stride 间隔必须大于零，不能包含月份或更大的单位。

10.4. AT TIME ZONE

AT TIME ZONE 操作符将没有时区的时间戳转换为带有时区的时间戳，反之亦然，并将 time with time zone 值转换为不同的时区。表 AT TIME ZONE 变体显示了它的变体。

表 AT TIME ZONE 变体

在这些表达式中，所需的时区 zone 可以指定为文本值（例如 'America/Los_Angeles'）或间隔（例如 INTERVAL '-08:00'）。例如（假设当前的 TimeZone 设置为 America/Los_Angeles）。

一些例子（假设当前的 TimeZone 设置为 America/Los_Angeles）：

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 18:38:40

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40

第一个示例为缺少时区的值添加时区，并使用当前的 TimeZone 设置显示该值。第二个示例将带有时区的时间戳值移动到指定的时区，并返回没有时区的值。这允许存储和显示与当前 TimeZone 设置不同的值。第三个示例将东京时间转换为芝加哥时间。

函数 timezone(zone, timestamp) 等效于符合 SQL 的构造 timestamp AT TIME ZONE zone。

10.5. 当前日期/时间

UXDB 提供了许多返回当前日期和时间的函数。这些 SQL 标准的函数全部都按照当前事务的开始时刻返回值：

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

CURRENT_TIME 和 CURRENT_TIMESTAMP 传递带有时区的值；LOCALTIME 和 LOCALTIMESTAMP 传递的值不带时区。

CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME 和 LOCALTIMESTAMP 可以有选择地接受一个精度参数，该精度导致结果的秒域被圆整为指定小数位。如果没有精度参数，结果将被给予所能得到的全部精度。

一些例子：

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2019-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2019-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2019-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2019-12-23 14:39:53.662522

由于这些函数返回当前事务的开始时间，因此它们的值在事务期间不会更改。这被认为是一个特性：其目的是允许单个事务具有“当前”时间的一致概念，从而使同一事务中的多个修改具有相同的时间戳。

注意
其他数据库系统可能更频繁地推进这些值。

UXsinoDB 还提供了函数返回当前语句的开始时间，以及实际的在调用函数时的当前时间。完整列表非 SQL 标准时间函数是：

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

transaction_timestamp() 等效于 CURRENT_TIMESTAMP，但命名为清晰反映它返回的内容。statement_timestamp() 返回当前的开始时间语句（更具体地说，是最新命令的接收时间来自客户端的消息）。statement_timestamp() 和 transaction_timestamp() 在事务的第一个命令期间返回相同的值，但可能会在后续命令期间不同。clock_timestamp 返回实际的当前时间，和因此其值甚至在单个 SQL 命令中也会发生变化。timeofday() 是一个历史 UXsinoDB 函数。像 clock_timestamp() 一样，它返回实际的当前时间，但作为格式化的 text 字符串而不是 timestamp 带有时区的值。now() 是传统的 UXsinoDB 等同于 transaction_timestamp()。

所有日期/时间数据类型也接受特殊的文字值使用 now 指定当前日期和时间（再次解释为事务开始时间）。因此，以下三个都返回相同的结果：

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';  -- 但请参见下面的提示

提示
在指定稍后要评估的值时，请勿使用第三种形式，例如表列的 DEFAULT 子句。系统将 now 转换为 timestamp，一旦解析常量，因此在需要默认值时，将使用表创建的时间！前两种形式将不会被评估，直到使用默认值，因为它们是函数调用。因此，它们将提供所需的默认行插入时间的行为。

10.6. 延迟执行

以下函数可用于延迟服务器进程的执行：

ux_sleep ( double precision )
ux_sleep_for ( interval )
ux_sleep_until ( timestamp with time zone )

ux_sleep 使当前会话的进程休眠，直到经过给定的秒数。可以指定小数秒延迟。ux_sleep_for 是一个方便的函数，允许将睡眠时间指定为 interval。ux_sleep_until 是一个方便的函数，用于需要特定唤醒时间的情况。例如：

SELECT ux_sleep(1.5);
SELECT ux_sleep_for('5 minutes');
SELECT ux_sleep_until('tomorrow 03:00');

注意
睡眠间隔的有效分辨率因平台而异；0.01 秒是常见的值。睡眠延迟将至少与指定的时间一样长。它可能会更长，具体取决于诸如服务器负载之类的因素。特别是，ux_sleep_until 不能保证在指定的时间精确唤醒，但它不会提前唤醒。

警告
在调用 ux_sleep 或其变体时，请确保您的会话不持有比必要更多的锁。否则，其他会话可能必须等待您的睡眠进程，从而减慢整个系统的速度。

10.7. date_format

• 功能

  date_format 将日期值格式化为特定格式，将参数 1（日期值）按照参数 2（特定格式语法结构）进行格式化。

• 函数

  date_format(date, format);
  

• 参数

  表 date_format 参数说明

  参数	说明
  date	要格式化的有效日期值，如 2022-05-16 14:24:09.024727。
  format	由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符 (%)，如 %Y、%a。支持的说明符如下表所示。

  表 date_format 说明符

  限定符	含义
  %a	三个字符缩写的工作日名称，例如：Mon、Tue、Wed 等
  %b	三个字符缩写的月份名称，例如：Jan、Feb、Mar 等
  %c	以数字表示的月份值，例如：1、2、3...12
  %D	英文后缀，例如：0th、1st、2nd 等的一个月之中的第几天
  %d	如果是 1 个数字（小于 10），那么一个月之中的第几天表示为加前导加 0，例如：00、01、02...31
  %e	无前导 0 的月份的日子，例如：1、2...31
  %f	微秒，范围在 000000...999999
  %H	24 小时格式的小时，前导加 0，例如：00、01...23
  %h	12 小时格式的小时，前导加 0，例如：01、02...12
  %I	与 %h 相同
  %i	分钟，前导加 0，例如：00、01...59
  %j	一年中的第几天，前导加 0，例如：001、002...366
  %k	24 小时格式的小时，无前导 0，例如：0、1、2...23
  %l	12 小时格式的小时，无前导 0，例如：0、1、2...12
  %M	月份全名称，例如：January、February...December
  %m	前导加 0 的月份名称，例如：00、01、02...12
  %p	AM 或 PM，取决于其他时间说明符
  %r	表示时间，12 小时格式 hh:mm:ss AM 或 PM
  %S	表示秒，前导加 0，例如：00、01...59
  %s	与 %S 相同
  %T	表示时间，24 小时格式 hh:mm:ss
  %U	周的第一天是星期日，前导加 0 的周数，例如：00、01、02...53
  %u	周的第一天是星期一，前导加 0 的周数，例如：00、01、02...53
  %V	与 %U 相同，与 %X 一起使用
  %v	与 %u 相同，与 %x 一起使用
  %W	工作日的全程，例如：Sunday、Monday...Saturday
  %w	以数字表示工作日（0=星期日，1=星期一...6=星期六）
  %X	周的四位数表示年份，第一天是星期日，常与 %V 一起使用
  %x	周的四位数表示年份，第一天是星期一，常与 %v 一起使用
  %Y	表示年份，四位数，例如：2000、2001 等
  %y	表示年份，两位数，例如：00、01 等
  %%	将百分比（%）字符添加到输出

• 返回值

  返回 text 类型。

• 示例

  select date_format('2022-05-16 14:24:09.024727','%W %D %M %Y');
  select date_format(now(),'%W %D %M %Y');
  select DATE_FORMAT(now(),'%a %b %d %f %H %h %I %i %j %M %m %p %S %s %W %Y %y %c %D %e %k %l %r %T %U %u %V %v %w %X %x');
  select date_format(now(),'%W - %D - %M - %Y test 123');