这是本节的多页打印视图。点击此处打印.

匹配和搜索函数

1: MATCH 子句函数

1.1: EVENT_NAME
1.2: MATCH_ID
1.3: PATTERN_ID

2: 正则表达式函数

2.1: MATCH_COLUMNS
2.2: REGEXP_COUNT
2.3: REGEXP_ILIKE
2.4: REGEXP_INSTR
2.5: REGEXP_LIKE
2.6: REGEXP_NOT_ILIKE
2.7: REGEXP_NOT_LIKE
2.8: REGEXP_REPLACE
2.9: REGEXP_SUBSTR

3: 文本搜索函数

3.1: DELETE_TOKENIZER_CONFIG_FILE
3.2: GET_TOKENIZER_PARAMETER
3.3: READ_CONFIG_FILE
3.4: SET_TOKENIZER_PARAMETER

此部分包含文本搜索和正则表达式函数，以及 MATCH 子句中使用的函数。

1 - MATCH 子句函数

与 MATCH 子句一起使用，此部分的函数返回关于查找或返回的模式的附加数据。例如，可以使用这些函数返回表示与输入行匹配的事件或模式名的值、匹配的序列名或匹配模式实例的全分区唯一标识符。

如果您想在点击流分析中根据用户的 Web 浏览行为（页面点击）确定用户的操作，那么模式匹配就尤其有用。典型的在线点击流漏斗是：

公司主页 -> 产品主页 -> 搜索 -> 结果 -> 在线购买

您可以使用以上点击流漏斗，在用户的 Web 点击序列中搜索匹配并标识该用户：

登录了公司主页。
导航至产品页面。
运行查询。
单击搜索结果中的链接。
购买。

有关使用此点击流模型的示例，请参阅事件系列模式匹配。

注意

GROUP BY 和 PARTITION BY 表达式不支持窗口函数。

1.1 - EVENT_NAME

返回一个 VARCHAR 值，表示与行匹配事件的名称。

语法

EVENT_NAME()

注意

模式匹配函数必须用在 MATCH 子句语法中；例如，如果单独调用 EVENT_NAME()，Vertica 将返回以下错误消息：

=> SELECT event_name();
ERROR:  query with pattern matching function event_name must include a MATCH clause

示例

注意

此示例使用在事件系列模式匹配中定义的架构。

以下语句分析了用户在 "website2.com" 的浏览历史并标识了用户从另一网站（进入）登录 website2.com 以及在做出购买之前（购买）浏览任意数量的其他页面（站内）的模式。该查询还将输出 EVENT_NAME() 的结果值，即与行匹配的事件的名称。

SELECT uid,
       sid,
       ts,
       refurl,
       pageurl,
       action,
       event_name()
FROM clickstream_log
MATCH
  (PARTITION BY uid, sid ORDER BY ts
   DEFINE
     Entry    AS RefURL  NOT ILIKE '%website2.com%' AND PageURL ILIKE '%website2.com%',
     Onsite   AS PageURL ILIKE     '%website2.com%' AND Action='V',
     Purchase AS PageURL ILIKE     '%website2.com%' AND Action = 'P'
   PATTERN
     P AS (Entry Onsite* Purchase)
   ROWS MATCH FIRST EVENT);
 uid | sid |    ts    |        refurl        |       pageurl        | action | event_name
-----+-----+----------+----------------------+----------------------+--------+------------
   1 | 100 | 12:00:00 | website1.com         | website2.com/home    | V      | Entry
   1 | 100 | 12:01:00 | website2.com/home    | website2.com/floby   | V      | Onsite
   1 | 100 | 12:02:00 | website2.com/floby   | website2.com/shamwow | V      | Onsite
   1 | 100 | 12:03:00 | website2.com/shamwow | website2.com/buy     | P      | Purchase
   2 | 100 | 12:10:00 | website1.com         | website2.com/home    | V      | Entry
   2 | 100 | 12:11:00 | website2.com/home    | website2.com/forks   | V      | Onsite
   2 | 100 | 12:13:00 | website2.com/forks   | website2.com/buy     | P      | Purchase
(7 rows)

另请参阅

1.2 - MATCH_ID

将成功模式匹配作为 INTEGER 值返回。返回值是分区中匹配所在的序号位置。

语法

MATCH_ID()

注意

模式匹配函数必须用在 MATCH 子句语法中；例如，如果单独调用 MATCH_ID()，Vertica 将返回以下错误消息：

=> SELECT match_id();
ERROR:  query with pattern matching function match_id must include a MATCH clause

示例

注意

此示例使用在事件系列模式匹配中定义的架构。

下面的语句在 website2.com 网站上分析用户的浏览历史，并确定用户从另一个网站（ website2.com 子句中的Entry ）到达 MATCH ，以及订购（购买）之前浏览的其他页面（Onsite）数的模式。查询同时也输出表示匹配序号的 MATCH_ID() 的值。

SELECT uid,
       sid,
       ts,
       refurl,
       pageurl,
       action,
       match_id()
FROM clickstream_log
MATCH
  (PARTITION BY uid, sid ORDER BY ts
   DEFINE
     Entry    AS RefURL  NOT ILIKE '%website2.com%' AND PageURL ILIKE '%website2.com%',
     Onsite   AS PageURL ILIKE     '%website2.com%' AND Action='V',
     Purchase AS PageURL ILIKE     '%website2.com%' AND Action = 'P'
   PATTERN
     P AS (Entry Onsite* Purchase)
   ROWS MATCH FIRST EVENT);

uid | sid |    ts    |        refurl        |       pageurl        | action | match_id
----+-----+----------+----------------------+----------------------+--------+------------
1   | 100 | 12:00:00 | website1.com         | website2.com/home    | V      |          1
1   | 100 | 12:01:00 | website2.com/home    | website2.com/floby   | V      |          2
1   | 100 | 12:02:00 | website2.com/floby   | website2.com/shamwow | V      |          3
1   | 100 | 12:03:00 | website2.com/shamwow | website2.com/buy     | P      |          4
2   | 100 | 12:10:00 | website1.com         | website2.com/home    | V      |          1
2   | 100 | 12:11:00 | website2.com/home    | website2.com/forks   | V      |          2
2   | 100 | 12:13:00 | website2.com/forks   | website2.com/buy     | P      |          3
(7 rows)

另请参阅

1.3 - PATTERN_ID

返回匹配模式实例的全分区唯一标识符整数值。

语法

PATTERN_ID()

注意

模式匹配函数必须用在 MATCH 子句语法中；例如，如果单独调用 PATTERN_ID()，Vertica 将返回以下错误消息：

=> SELECT pattern_id();
ERROR:  query with pattern matching function pattern_id must include a MATCH clause

示例

注意

此示例使用在事件系列模式匹配中定义的架构。

以下语句分析了用户在 website2.com 的浏览历史并标识了用户从另一网站（进入）登录 website2.com 以及在做出购买之前（购买）浏览任意数量的其他页面（站内）的模式。查询也输出了 PATTERN_ID() 的值，表示匹配模式实例的全分区标识符。

SELECT uid,
       sid,
       ts,
       refurl,
       pageurl,
       action,
       pattern_id()
FROM clickstream_log
MATCH
  (PARTITION BY uid, sid ORDER BY ts
   DEFINE
     Entry    AS RefURL  NOT ILIKE '%website2.com%' AND PageURL ILIKE '%website2.com%',
     Onsite   AS PageURL ILIKE     '%website2.com%' AND Action='V',
     Purchase AS PageURL ILIKE     '%website2.com%' AND Action = 'P'
   PATTERN
     P AS (Entry Onsite* Purchase)
   ROWS MATCH FIRST EVENT);


uid | sid |    ts    |        refurl        |       pageurl        | action | pattern_id
----+-----+----------+----------------------+----------------------+--------+------------
1   | 100 | 12:00:00 | website1.com         | website2.com/home    | V      |          1
1   | 100 | 12:01:00 | website2.com/home    | website2.com/floby   | V      |          1
1   | 100 | 12:02:00 | website2.com/floby   | website2.com/shamwow | V      |          1
1   | 100 | 12:03:00 | website2.com/shamwow | website2.com/buy     | P      |          1
2   | 100 | 12:10:00 | website1.com         | website2.com/home    | V      |          1
2   | 100 | 12:11:00 | website2.com/home    | website2.com/forks   | V      |          1
2   | 100 | 12:13:00 | website2.com/forks   | website2.com/buy     | P      |          1
(7 rows)

另请参阅

2 - 正则表达式函数

利用正则表达式可以对字符串执行模式匹配。利用正则表达式语法，可以精确地定义用于匹配字符串的模式，从而为您提供比 LIKE 谓词中所用的通配符匹配更多的控制。利用 Vertica 正则表达式函数可以执行多种任务，例如确定字符串值是否与某个模式匹配，提取字符串中与某个模式匹配的部分，或对字符串中某个模式出现的次数进行计数。

Vertica 使用兼容 Perl 的正则表达式 (PCRE) 库来为正则表达式求值。顾名思义，PCRE 的正则表达式语法可兼容 Perl 5 编程语言所用的语法。您可以阅读 PCRE 文档中关于其库的内容。但如果您不熟悉正则表达式的使用，Perl 正则表达式文档提供了很好的入门材料。

注意

仅可对有效的 UTF-8 字符串运行正则表达式函数。如果尝试对非有效 UTF-8 的字符串运行正则表达式函数，查询将会失败并显示错误。为防止出错，请使用 ISUTF8 函数作为初始子句，以确保传递到正则表达式函数的字符串是有效的 UTF-8 字符串。或者也可以使用“b”参数将字符串作为二进制八位字节，而不是 UTF-8 编码字符串进行处理。

2.1 - MATCH_COLUMNS

指定为 SELECT 列表中的元素，返回查询表中与指定模式匹配的所有列。例如：

=> SELECT MATCH_COLUMNS ('%order%') FROM store.store_orders_fact LIMIT 3;
 order_number | date_ordered | quantity_ordered | total_order_cost | reorder_level
--------------+--------------+------------------+------------------+---------------
       191119 | 2003-03-09   |               15 |             4021 |            23
        89985 | 2003-05-04   |               19 |             2692 |            23
       246962 | 2007-06-01   |               77 |             4419 |            42
(3 rows)

语法

MATCH_COLUMNS ('pattern')

参数

模式

匹配查询表中所有列名的模式，其中 pattern 通常包含以下一个或两个通配符：

_ （下划线）：匹配任意单个字符。
% （百分号）：匹配 0 或多个字符的字符串。

模式还可以包含反斜杠 (\\) 字符，转义嵌入列名中的保留字符：_（下划线）、%（百分号）和反斜杠 (\\) 本身。

特权

无

DDL 的用法

您可以使用 MATCH_COLUMNS 定义数据库对象 — 例如，在 CREATE PROJECTION 中指定来标识投影列，或者在 CREATE TABLE...AS 中标识新表中的列。在所有情况下，Vertica 都会在存储对象 DDL 之前扩展 MATCH_COLUMNS 输出。对原始源表的后续更改不会影响派生的对象定义。

限制

通常，将 MATCH_COLUMNS 指定为 SELECT 列表中的一个元素。例如，CREATE PROJECTION 可调用 MATCH_COLUMNS 来指定要包含在投影中的列。但是，尝试在投影的分段子句中指定列会返回错误：

=> CREATE PROJECTION p_store_orders AS SELECT
    MATCH_COLUMNS('%product%'),
    MATCH_COLUMNS('%store%'),
    order_number FROM store.store_orders_fact SEGMENTED BY MATCH_COLUMNS('products%') ALL NODES;
ERROR 0:  MATCH_COLUMNS() function can only be specified as an element in a SELECT list

=> CREATE PROJECTION p_store_orders AS SELECT
    MATCH_COLUMNS('%product%'),
    MATCH_COLUMNS('%store%'),
    order_number FROM store.store_orders_fact;
WARNING 4468:  Projection <store.p_store_orders_b0> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
WARNING 4468:  Projection <store.p_store_orders_b1> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

如果从支持固定数量实参的函数调用 MATCH_COLUMNS，则 Vertica 将返回错误。例如，UPPER 函数仅支持一个实参；因此，按照以下方式从 UPPER 调用 MATCH_COLUMNS 将返回错误：

=> SELECT MATCH_COLUMNS('emp%') FROM employee_dimension LIMIT 1;
-[ RECORD 1 ]-----------+---------------------------------
employee_key            | 1
employee_gender         | Male
employee_first_name     | Craig
employee_middle_initial | F
employee_last_name      | Robinson
employee_age            | 22
employee_street_address | 5 Bakers St
employee_city           | Thousand Oaks
employee_state          | CA
employee_region         | West

=> SELECT UPPER (MATCH_COLUMNS('emp%')) FROM employee_dimension;
ERROR 10465:  MATCH_COLUMNS() function can only be specified as an element in a SELECT list

相反，HASH 函数接受无限数量的实参，因此可以成功将 MATCH_COLUMNS 作为实参调用：

=> select HASH(MATCH_COLUMNS('emp%')) FROM employee_dimension LIMIT 10;
        HASH
---------------------
 2047284364908178817
 1421997332260827278
 7981613309330877388
  792898558199431621
 5275639269069980417
 7892790768178152349
  184601038712735208
 3020263228621856381
 7056305566297085916
 3328422577712931057
(10 rows)

其他约束

MATCH_COLUMNS 的以下用法无效并返回错误：

在 RECURSIVE WITH 子句的非递归（基础）术语查询中包含 MATCH_COLUMNS

连接 MATCH_COLUMNS 调用的结果：

=> SELECT MATCH_COLUMNS ('%store%')||MATCH_COLUMNS('%store%') FROM store.store_orders_fact;
ERROR 0:  MATCH_COLUMNS() function can only be specified as an element in a SELECT list

在 MATCH_COLUMNS 上设置别名

示例

以下 CREATE PROJECTION 语句使用 MATCH_COLUMNS 指定新投影中的表列：

=> CREATE PROJECTION p_store_orders AS SELECT
    MATCH_COLUMNS('%product%'),
    MATCH_COLUMNS('%store%'),
    order_number FROM store.store_orders_fact;
WARNING 4468:  Projection <store.p_store_orders_b0> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
WARNING 4468:  Projection <store.p_store_orders_b1> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

=> SELECT export_objects('', 'store.p_store_orders_b0');
...

CREATE PROJECTION store.p_store_orders_b0 /*+basename(p_store_orders)*/
(
 product_key,
 product_version,
 store_key,
 order_number
)
AS
 SELECT store_orders_fact.product_key,
        store_orders_fact.product_version,
        store_orders_fact.store_key,
        store_orders_fact.order_number
 FROM store.store_orders_fact
 ORDER BY store_orders_fact.product_key,
          store_orders_fact.product_version,
          store_orders_fact.store_key,
          store_orders_fact.order_number
SEGMENTED BY hash(store_orders_fact.product_key, store_orders_fact.product_version, store_orders_fact.store_key, store_orders_fact.order_number) ALL NODES OFFSET 0;

SELECT MARK_DESIGN_KSAFE(1);

(1 row)

如 EXPORT_OBJECTS 输出所示，Vertica 将两个 MATCH_COLUMNS 调用的结果集存储到新投影的 DDL 中。锚表 DDL 中的后续更改不会影响此投影。

2.2 - REGEXP_COUNT

返回正则表达式与字符串匹配的次数。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_COUNT ( string-expession, pattern [, position [, regexp‑modifier ]... ] )

参数

string-expression

VARCHAR 或 LONG VARCHAR 表达式，用于评估在模式中指定的正则表达式是否匹配。如果字符串表达式位于在 Flex 表或列式表的 __raw__ 列中，则在搜索模式之前将字符串转换为 LONG VARCHAR。

模式

与字符串表达式匹配的正则表达式。正则表达式必须符合 Perl 正则表达式语法。

position

函数应该从自字符串开头算起的多少个字符开始搜索匹配项。默认情况下，函数从第一个（最左侧）字符开始搜索匹配项。如果将此参数设置为大于 1 的值，将从您指定的第 n 个字符开始搜索匹配项。

默认值： 1

regexp‑modifier

[可选] 一个或多个单字符标志，可修改正则表达式模式与字符串表达式匹配的方式：

b：将字符串视为二进制八位字节数，而不是 UTF-8 字符。
c （默认值）：强制匹配区分大小写。
i：强制匹配不区分大小写。
m：将要匹配的字符串视为多行。使用此修饰符，可使行头 (^) 和行尾 ($) 的正则表达式运算符与字符串内的换行符 (\n) 相匹配。如果没有 m 修饰符，行头和行尾运算符仅与字符串的开头和结尾相匹配。
n：将正则表达式运算符 (.) 与换行符 (\n) 相匹配。默认情况下，. 运算符可与除换行符以外的任何字符相匹配。
x：将注释添加到正则表达式。x 修饰符可导致函数忽略正则表达式中所有未经转义的空格字符和注释。注释以哈希 (#) 字符开始，以换行符 (\n) 结束。在字符串中匹配的正则表达式中的所有空格都必须使用反斜杠 (\\) 字符进行转义。

示例

计算指定字符串 (a man, a plan, a canal: Panama) 中子字符串 an 的出现次数：

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', 'an');
 REGEXP_COUNT
--------------
            4
(1 row)

自第五个字符开始，查找子字符串 an 的出现次数。

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', 'an',5);
 REGEXP_COUNT
--------------
            3
(1 row)

查找包含小写字符且后跟 an 的子字符串的出现次数：

=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', '[a-z]an');
 REGEXP_COUNT
--------------
            3
(1 row

REGEXP_COUNT 指定 i 修饰符，因此忽略大小写：


=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', '[a-z]an', 1, 'i');

 REGEXP_COUNT
--------------
            4

2.3 - REGEXP_ILIKE

如果字符串包含正则表达式的匹配项，则返回 True。REGEXP_ILIKE 与 LIKE 谓词相似，不同的是它使用不区分大小写的正则表达式，而不是简单的通配符匹配。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_ILIKE ( string-expression, pattern )

参数

示例

此示例将创建一个包含多个字符串的表来演示正则表达式。

使用单个 LONG VARCHAR 列 longvc 创建表 (body)，并插入具有不同字符的数据：

=> CREATE table longvc(body long varchar (1048576));
CREATE TABLE


=> insert into longvc values ('На берегу пустынных волн');
=> insert into longvc values ('Voin syödä lasia, se ei vahingoita minua');
=> insert into longvc values ('私はガラスを食べられます。それは私を傷つけません。');
=> insert into longvc values ('Je peux manger du verre, ça ne me fait pas mal.');
=> insert into longvc values ('zésbaésbaa');
=> insert into longvc values ('Out of the frying pan, he landed immediately in the fire');


=> SELECT * FROM longvc;
                      body
------------------------------------------------
 На берегу пустынных волн
 Voin syödä lasia, se ei vahingoita minua
 私はガラスを食べられます。それは私を傷つけません。
 Je peux manger du verre, ça ne me fait pas mal.
 zésbaésbaa
 Out of the frying pan, he landed immediately in the fire
(6 rows)

包含字符 ç 的模式匹配表行：

=> SELECT * FROM longvc where regexp_ilike(body, 'ç');
                      body
-------------------------------------------------
 Je peux manger du verre, ça ne me fait pas mal.
(1 row)

选择所有包含字符 A/a 的行：

=> SELECT * FROM longvc where regexp_ilike(body, 'A');
                      body
-------------------------------------------------
 Je peux manger du verre, ça ne me fait pas mal.
 Voin syödä lasia, se ei vahingoita minua
 zésbaésbaa
(3 rows)

选择所有包含字符 O/o 的行：

=> SELECT * FROM longvc where regexp_ilike(body, 'O');
                           body
----------------------------------------------------------
 Voin syödä lasia, se ei vahingoita minua
 Out of the frying pan, he landed immediately in the fire
(2 rows)

2.4 - REGEXP_INSTR

返回字符串中正则表达式所匹配的开始或结束位置。如果正则表达式在字符串中没有找到匹配项，REGEXP_INSTR 将返回 0。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_INSTR ( string-expression, pattern 
   [, position [, occurrence [, return‑position [, regexp‑modifier ]... [, captured‑subexp ]]]] )

参数

string-expression

模式

与字符串表达式匹配的正则表达式。正则表达式必须符合 Perl 正则表达式语法。

position

默认值： 1

occurrence

控制要返回字符串中第几次出现的模式匹配项。默认情况下，函数返回第一个匹配子字符串的位置。使用此参数可以查找后续匹配子字符串的位置。例如，如果将此参数设置为 3，将返回与模式匹配的第三个子字符串的位置。

默认值： 1

return‑position

设置字符串内要返回的位置。如果使用默认位置 (0)，函数将返回与模式匹配的子字符串的第一个字符的字符串位置。如果将 return‑position 设为 1，函数将返回匹配的子字符串结束后的第一个字符的位置。

默认值： 0

regexp‑modifier

[可选] 一个或多个单字符标志，可修改正则表达式模式与字符串表达式匹配的方式：

b：将字符串视为二进制八位字节数，而不是 UTF-8 字符。
c （默认值）：强制匹配区分大小写。
i：强制匹配不区分大小写。
m：将要匹配的字符串视为多行。使用此修饰符，可使行头 (^) 和行尾 ($) 的正则表达式运算符与字符串内的换行符 (\n) 相匹配。如果没有 m 修饰符，行头和行尾运算符仅与字符串的开头和结尾相匹配。
n：将正则表达式运算符 (.) 与换行符 (\n) 相匹配。默认情况下，. 运算符可与除换行符以外的任何字符相匹配。
x：将注释添加到正则表达式。x 修饰符可导致函数忽略正则表达式中所有未经转义的空格字符和注释。注释以哈希 (#) 字符开始，以换行符 (\n) 结束。在字符串中匹配的正则表达式中的所有空格都必须使用反斜杠 (\\) 字符进行转义。

captured‑subexp

要返回其位置的括住的子表达式。默认情况下，函数将返回 string 中与正则表达式匹配的第一个字符的位置。如果将此值设置为从 1 到 9，函数将返回由正则表达式中相应的一组圆括号括住的子表达式。例如，如果将此值设置为 3，将返回由正则表达式中第三组圆括号括住的子字符串。

默认值： 0

注意

子表达式按左括号的出现位置从左到右编号，与嵌套的正则表达式一样。例如，在正则表达式 \s*(\w+\s+(\w+)) 中，子表达式 1 包括了除前导空格之外的所有文本。

示例

在指定字符串 (easy come, easy go) 中查找以字母 e 开头并以字母 y 结尾的字母序列的第一个实例。

=> SELECT REGEXP_INSTR('easy come, easy go','e\w*y');
 REGEXP_INSTR
--------------
            1
(1 row)

自第二个字符 (2) 开始，查找以字母 e 开头并以字母 y 结尾的第一个字母序列：

=> SELECT REGEXP_INSTR('easy come, easy go','e\w*y',2);
 REGEXP_INSTR
--------------
           12
(1 row)

自第一个字符 (1) 开始，查找以字母 e 开头并以字母 y 结尾的第二个字母序列：

=> SELECT REGEXP_INSTR('easy come, easy go','e\w*y',1,2);
 REGEXP_INSTR
--------------
           12
(1 row)

查找第一个空格之后的第一个字符的位置：

=> SELECT REGEXP_INSTR('easy come, easy go','\s',1,1,1);
 REGEXP_INSTR
--------------
            6
(1 row)

通过将每个单词捕获为一个子表达式并返回第三个子表达式的开始位置，查找字符串中第三个单词的开始位置。

=> SELECT REGEXP_INSTR('one two three','(\w+)\s+(\w+)\s+(\w+)', 1,1,0,'',3);
REGEXP_INSTR
--------------
            9
(1 row)

2.5 - REGEXP_LIKE

如果字符串与正则表达式匹配，则返回 True。REGEXP_LIKE 与 LIKE 谓词相似，不同的是它使用正则表达式而不是简单的通配符匹配。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_LIKE ( string-expression, pattern [, regexp‑modifier ]... )

参数

string-expression

模式

与字符串表达式匹配的正则表达式。正则表达式必须符合 Perl 正则表达式语法。

regexp‑modifier

[可选] 一个或多个单字符标志，可修改正则表达式模式与字符串表达式匹配的方式：

b：将字符串视为二进制八位字节数，而不是 UTF-8 字符。
c （默认值）：强制匹配区分大小写。
i：强制匹配不区分大小写。
m：将要匹配的字符串视为多行。使用此修饰符，可使行头 (^) 和行尾 ($) 的正则表达式运算符与字符串内的换行符 (\n) 相匹配。如果没有 m 修饰符，行头和行尾运算符仅与字符串的开头和结尾相匹配。
n：将正则表达式运算符 (.) 与换行符 (\n) 相匹配。默认情况下，. 运算符可与除换行符以外的任何字符相匹配。
x：将注释添加到正则表达式。x 修饰符可导致函数忽略正则表达式中所有未经转义的空格字符和注释。注释以哈希 (#) 字符开始，以换行符 (\n) 结束。在字符串中匹配的正则表达式中的所有空格都必须使用反斜杠 (\\) 字符进行转义。

示例

创建一个包含多个字符串的表：

=> CREATE TABLE t (v VARCHAR);
CREATE TABLE
=> CREATE PROJECTION t1 AS SELECT * FROM t;
CREATE PROJECTION
=> COPY t FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> aaa
>> Aaa
>> abc
>> abc1
>> 123
>> \.
=> SELECT * FROM t;
   v
-------
 aaa
 Aaa
 abc
 abc1
 123
(5 rows)

选择表 t 中所有包含字母 a 的记录：

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'a');
  v
------
 Aaa
 aaa
 abc
 abc1
(4 rows)

选择表 t 中所有以字母 a 开头的行：

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'^a');
  v
------
 aaa
 abc
 abc1
(3 rows)

选择所有包含子字符串 aa 的行：

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'aa');
  v
-----
 Aaa
 aaa
(2 rows)

选择所有包含一个数字的行。

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'\d');
  v
------
 123
 abc1
(2 rows)

选择所有包含子字符串 aaa 的行：

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'aaa');
  v
-----
 aaa
(1 row)

选择所有包含子字符串 aaa 且使用不区分大小写的匹配的行。

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'aaa', 'i');
  v
-----
 Aaa
 aaa
(2 rows)

选择包含子字符串 a b c 的行：

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'a b c');
 v
---
(0 rows)

选择包含子字符串 a b c 且忽略正则表达式内的空格的行。

=> SELECT v FROM t WHERE REGEXP_LIKE(v,'a b c','x');
  v
------
 abc
 abc1
(2 rows)

向表 t 中添加多行行：

=> COPY t FROM stdin RECORD TERMINATOR '!';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Record 1 line 1
>> Record 1 line 2
>> Record 1 line 3!
>> Record 2 line 1
>> Record 2 line 2
>> Record 2 line 3!
>> \.

选择表 t 中以子字符串 Record 开头并以子字符串 line 2 结尾的行。

=> SELECT v from t WHERE REGEXP_LIKE(v,'^Record.*line 2$');
 v
---
(0 rows)

选择以子字符串 Record 开头并以子字符串 line 2 结尾，并且将多个行作为单独字符串处理的行。

=> SELECT v from t WHERE REGEXP_LIKE(v,'^Record.*line 2$','m');
                        v
--------------------------------------------------

Record 2 line 1
Record 2 line 2
Record 2 line 3
Record 1 line 1
Record 1 line 2
Record 1 line 3
(2 rows)

2.6 - REGEXP_NOT_ILIKE

如果字符串与不区分大小写的正则表达式不匹配，则返回 True。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_NOT_ILIKE ( string-expression, pattern )

参数

示例

创建一个包含单个 long varchar 列 (longvc) 的表 (body)。然后，插入包含一些不同字符的数据，并查询表内容：

=> CREATE table longvc(body long varchar (1048576));
CREATE TABLE


=> insert into longvc values ('На берегу пустынных волн');
=> insert into longvc values ('Voin syödä lasia, se ei vahingoita minua');
=> insert into longvc values ('私はガラスを食べられます。それは私を傷つけません。');
=> insert into longvc values ('Je peux manger du verre, ça ne me fait pas mal.');
=> insert into longvc values ('zésbaésbaa');


=> SELECT * FROM longvc;
                      body
------------------------------------------------
 На берегу пустынных волн
 Voin syödä lasia, se ei vahingoita minua
 私はガラスを食べられます。それは私を傷つけません。
 Je peux manger du verre, ça ne me fait pas mal.
 zésbaésbaa
(5 rows)

查找所有不包含字符 ç 的行：


=> SELECT * FROM longvc where regexp_not_ilike(body, 'ç');
                        body
----------------------------------------------------
 Voin syödä lasia, se ei vahingoita minua
 zésbaésbaa
 На берегу пустынных волн
 私はガラスを食べられます。それは私を傷つけません。
(4 rows)

查找所有不包含子字符串 a 的行：

=> SELECT * FROM longvc where regexp_not_ilike(body, 'a');
                        body
----------------------------------------------------
 На берегу пустынных волн
 私はガラスを食べられます。それは私を傷つけません。
(2 rows)

2.7 - REGEXP_NOT_LIKE

如果字符串不包含正则表达式的匹配项，则返回 True。REGEXP_NOT_LIKE 是区分大小写的正则表达式。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_NOT_LIKE ( string-expression, pattern )

参数

示例

创建一个包含 LONG VARCHAR 列 body 的表 (longvc)。然后，插入包含一些不同字符的数据，并查询表内容：

=> CREATE table longvc(body long varchar (1048576));
CREATE TABLE


=> insert into longvc values ('На берегу пустынных волн');
=> insert into longvc values ('Voin syödä lasia, se ei vahingoita minua');
=> insert into longvc values ('私はガラスを食べられます。それは私を傷つけません。');
=> insert into longvc values ('Je peux manger du verre, ça ne me fait pas mal.');
=> insert into longvc values ('zésbaésbaa');


=> SELECT * FROM longvc;
                      body
------------------------------------------------
 На берегу пустынных волн
 Voin syödä lasia, se ei vahingoita minua
 私はガラスを食べられます。それは私を傷つけません。
 Je peux manger du verre, ça ne me fait pas mal.
 zésbaésbaa
(5 rows)

使用 REGEXP_NOT_LIKE 可以返回不包含字符 ç 的行：

=> SELECT * FROM longvc where regexp_not_like(body, 'ç');
                        body
----------------------------------------------------
 Voin syödä lasia, se ei vahingoita minua
 zésbaésbaa
 На берегу пустынных волн
 私はガラスを食べられます。それは私を傷つけません。
(4 rows)

返回不包含字符 *ö 和 *ä 的所有行：

=> SELECT * FROM longvc where regexp_not_like(body, '.*ö.*ä');
                        body
----------------------------------------------------
 Je peux manger du verre, ça ne me fait pas mal.
 zésbaésbaa
 На берегу пустынных волн
 私はガラスを食べられます。それは私を傷つけません。
(4 rows)

对不包含子字符 z 和 *ésbaa 的所有行进行模式匹配：

=> SELECT * FROM longvc where regexp_not_like(body, 'z.*ésbaa');
                        body
----------------------------------------------------
 Je peux manger du verre, ça ne me fait pas mal.
 Voin syödä lasia, se ei vahingoita minua
 zésbaésbaa
 На берегу пустынных волн
 私はガラスを食べられます。それは私を傷つけません。
(5 rows)

2.8 - REGEXP_REPLACE

将所有与正则表达式匹配的子字符串实例替换为另一个子字符串。REGEXP_REPLACE 与 REPLACE 函数相似，不同的是，它使用正则表达式来选择要替换的子字符串。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_REPLACE ( string-expression, target 
   [, replacement [, position [, occurrence[...] [, regexp‑modifier]]]] )

参数

string-expression

模式

与字符串表达式匹配的正则表达式。正则表达式必须符合 Perl 正则表达式语法。

replacement

用于替换匹配的子字符串的字符串。如果不提供 replacement，函数将会删除匹配的子字符串。替换字符串可能包含正则表达式所捕获的子字符串的 baccalaureate。第一个捕获的子字符串将会使用 \1 插入到替换字符串，第二个则会使用 \2 插入到替换字符串，依此类推。

position

默认值： 1

occurrence

默认值： 1

regexp‑modifier

[可选] 一个或多个单字符标志，可修改正则表达式模式与字符串表达式匹配的方式：

b：将字符串视为二进制八位字节数，而不是 UTF-8 字符。
c （默认值）：强制匹配区分大小写。
i：强制匹配不区分大小写。
m：将要匹配的字符串视为多行。使用此修饰符，可使行头 (^) 和行尾 ($) 的正则表达式运算符与字符串内的换行符 (\n) 相匹配。如果没有 m 修饰符，行头和行尾运算符仅与字符串的开头和结尾相匹配。
n：将正则表达式运算符 (.) 与换行符 (\n) 相匹配。默认情况下，. 运算符可与除换行符以外的任何字符相匹配。
x：将注释添加到正则表达式。x 修饰符可导致函数忽略正则表达式中所有未经转义的空格字符和注释。注释以哈希 (#) 字符开始，以换行符 (\n) 结束。在字符串中匹配的正则表达式中的所有空格都必须使用反斜杠 (\\) 字符进行转义。

Oracle 如何处理子表达式

与 Oracle 不同，Vertica 可以处理捕获到的无限数量的子表达式，而 Oracle 则限制为 9 个。

在 Vertica 中，可以在替换模式中使用 \10 来访问正则表达式中第 10 对圆括号所捕获的子字符串。在 Oracle 中，\10 将作为第一对圆括号所捕获的子字符串进行处理，后面紧跟 0。要在 Vertica 强制使用此 Oracle 行为，请使用 \g 反向引用，并将捕获的子表达式编号括在花括号内。例如，\g{1}0 表示第一对圆括号所捕获的子字符串，后面紧跟 0。

也可以对捕获的子表达式进行命名，以增强正则表达式的明确性。有关详细信息，请参阅 PCRE 文档。

示例

在字符串 healthy, wealthy, and wise 中查找以 thy 结尾的“字符元”组（字母、数字和下划线），并将其替换为空字符串。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy');
 REGEXP_REPLACE
----------------
 , , and wise
(1 row)

查找以 thy 结尾的字符元组，并替换为字符串 something。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something');
         REGEXP_REPLACE
--------------------------------
 something, something, and wise
(1 row)

查找以 thy 结尾的字符元组，并从字符串中的第三个字符开始替换为字符串 something。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something', 3);
          REGEXP_REPLACE
----------------------------------
 hesomething, something, and wise
(1 row)

将以 thy 结尾的第二个字符元组替换为字符串 something。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something', 1, 2);
        REGEXP_REPLACE
------------------------------
 healthy, something, and wise
(1 row)

查找以 thy 结尾的字符元组（捕获 thy 前面的字母），并替换为捕获到的字母加上字母 ish。

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','(\w+)thy', '\1ish');
       REGEXP_REPLACE
----------------------------
 healish, wealish, and wise
(1 row)

创建表以演示替换查询中的字符串。

=> CREATE TABLE customers (name varchar(50), phone varchar(11));
CREATE TABLE
=> CREATE PROJECTION customers1 AS SELECT * FROM customers;
CREATE PROJECTION
=> COPY customers FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Able, Adam|17815551234
>> Baker,Bob|18005551111
>> Chu,Cindy|16175559876
>> Dodd,Dinara|15083452121
>> \.

查询客户，使用 REGEXP_REPLACE 设置电话号码格式。

=> SELECT name, REGEXP_REPLACE(phone, '(\d)(\d{3})(\d{3})(\d{4})',
'\1-(\2) \3-\4') as phone FROM customers;
    name     |      phone
-------------+------------------
 Able, Adam  | 1-(781) 555-1234
 Baker,Bob   | 1-(800) 555-1111
 Chu,Cindy   | 1-(617) 555-9876
 Dodd,Dinara | 1-(508) 345-2121
(4 rows)

2.9 - REGEXP_SUBSTR

返回字符串内与正则表达式匹配的子字符串。如果没有找到匹配项，REGEXP_SUBSTR 将返回 NULL。这与空字符串不同，如果正则表达式匹配零长度的字符串，则会返回空字符串。

此函数使用默认区域设置对 UTF-8 字符串进行操作，即使区域设置设置为其他方式。

重要

如果要从 Oracle 数据库转移正则表达式查询，请记住 Oracle 将零长度字符串视为等同于 NULL，而 Vertica 却不会这样。

语法

REGEXP_SUBSTR ( string-expression, pattern 
   [, position [,  occurrence [, regexp‑modifier [, captured‑subexp ]]... ]] )

参数

string-expression

模式

与字符串表达式匹配的正则表达式。正则表达式必须符合 Perl 正则表达式语法。

position

默认值： 1

occurrence

默认值： 1

regexp‑modifier

[可选] 一个或多个单字符标志，可修改正则表达式模式与字符串表达式匹配的方式：

b：将字符串视为二进制八位字节数，而不是 UTF-8 字符。
c （默认值）：强制匹配区分大小写。
i：强制匹配不区分大小写。
m：将要匹配的字符串视为多行。使用此修饰符，可使行头 (^) 和行尾 ($) 的正则表达式运算符与字符串内的换行符 (\n) 相匹配。如果没有 m 修饰符，行头和行尾运算符仅与字符串的开头和结尾相匹配。
n：将正则表达式运算符 (.) 与换行符 (\n) 相匹配。默认情况下，. 运算符可与除换行符以外的任何字符相匹配。
x：将注释添加到正则表达式。x 修饰符可导致函数忽略正则表达式中所有未经转义的空格字符和注释。注释以哈希 (#) 字符开始，以换行符 (\n) 结束。在字符串中匹配的正则表达式中的所有空格都必须使用反斜杠 (\\) 字符进行转义。

captured‑subexp

默认值： 0

注意

示例

选择以 thy 结尾的第一个字母子字符串。

=> SELECT REGEXP_SUBSTR('healthy, wealthy, and wise','\w+thy');
 REGEXP_SUBSTR
---------------
 healthy
(1 row)

选择从字符串中的第二个字符开头并以 thy 结尾的第一个字母子字符串。

=> SELECT REGEXP_SUBSTR('healthy, wealthy, and wise','\w+thy',2);
 REGEXP_SUBSTR
---------------
 ealthy
(1 row)

选择以 thy 结尾的第二个字母子字符串。

=> SELECT REGEXP_SUBSTR('healthy, wealthy, and wise','\w+thy',1,2);
 REGEXP_SUBSTR
---------------
 wealthy
(1 row)

返回捕获的第三个子表达式的内容，该子表达式将捕获字符串中的第三个词。

=> SELECT REGEXP_SUBSTR('one two three', '(\w+)\s+(\w+)\s+(\w+)', 1, 1, '', 3);
 REGEXP_SUBSTR
---------------
 three
(1 row)

3 - 文本搜索函数

此部分包含 Vertica 专用的文本搜索函数。

3.1 - DELETE_TOKENIZER_CONFIG_FILE

删除分词器配置文件。

语法

SELECT v_txtindex.DELETE_TOKENIZER_CONFIG_FILE (USING PARAMETERS proc_oid='proc_oid', confirm={true | false });

参数

confirm = [true | false]

Boolean 标志。表示应删除该配置文件，即使分词器仍在使用。

True — 当使用的参数值为 True 时，强制删除分词器。

False — 当使用的参数值为 False 时，删除分词器。

默认值： False

proc_oid

在创建分词器时为其分配的唯一标识符。用户必须查询系统表 vs_procedures 以为给定分词器名称获取 proc_oid。有关详细信息，请参阅配置分词器。

示例

以下示例说明如何使用 DELETE_TOKENIZER_CONFIG_FILE 删除分词器配置文件：

=> SELECT v_txtindex.DELETE_TOKENIZER_CONFIG_FILE (USING PARAMETERS proc_oid='45035996274126984');
 DELETE_TOKENIZER_CONFIG_FILE
------------------------------
 t
(1 row)

3.2 - GET_TOKENIZER_PARAMETER

返回给定分词器的配置参数。

语法

SELECT v_txtindex.GET_TOKENIZER_PARAMETER(parameter_name USING PARAMETERS proc_oid='proc_oid');

参数

parameter_name

要返回的参数的名称。

以下几项之一：

stopWordsCaseInsensitive
minorSeparators
majorSeparators
minLength
maxLength
ngramsSize
used

proc_oid

在创建分词器时为其分配的唯一标识符。用户必须查询系统表 vs_procedures 以为给定分词器名称获取 proc_oid。有关详细信息，请参阅配置分词器。

示例

以下示例显示了如何使用 GET_TOKENIZER_PARAMETER。

返回分词器中使用的停止词：

=> SELECT v_txtindex.GET_TOKENIZER_PARAMETER('stopwordscaseinsensitive' USING PARAMETERS proc_oid='45035996274126984');
 getTokenizerParameter
-----------------------
 devil,TODAY,the,fox
(1 row)

返回分词器中使用的主要分隔符：

=> SELECT v_txtindex.GET_TOKENIZER_PARAMETER('majorseparators' USING PARAMETERS proc_oid='45035996274126984');
 getTokenizerParameter
-----------------------
 {}()&[]
(1 row)

3.3 - READ_CONFIG_FILE

读取并返回给定分词器的所有参数的键值对。

您必须将 OVER() 子句与此函数一起使用。

语法

SELECT v_txtindex.READ_CONFIG_FILE(USING PARAMETERS proc_oid='proc_oid') OVER ()

参数

proc_oid: 在创建分词器时为其分配的唯一标识符。用户必须查询系统表 vs_procedures 以为给定分词器名称获取 proc_oid。有关详细信息，请参阅配置分词器。

示例

以下示例显示了如何使用 READ_CONFIG_FILE 来返回与分词器关联的参数：

=> SELECT v_txtindex.READ_CONFIG_FILE(USING PARAMETERS proc_oid='45035996274126984') OVER();
                config_key | config_value
 --------------------------+---------------------
  majorseparators          | {}()&[]
  stopwordscaseinsensitive | devil,TODAY,the,fox
(2 rows)

3.4 - SET_TOKENIZER_PARAMETER

配置分词器参数。

重要

\n, \t,\r 必须通过 Vertica 表示法 U&’\000D’ 或通过 Vertica 转义表示法 E’\r’ 以 Unicode 形式输入。否则，它们将被直接视为两个分隔符。例如 "\\" & "r"。

语法

SELECT v_txtindex.SET_TOKENIZER_PARAMETER (parameter_name, parameter_value USING PARAMETERS proc_oid='proc_oid')

参数

parameter_name

要配置的参数的名称。

使用下列内容之一：

stopwordsCaseInsensitive：非索引字列表。属于此列表的所有标记都会被忽略。Vertica 支持最多包含前 256 个 Unicode 字符的分隔符和非索引字。

如果您要定义包含逗号或反斜杠的非索引字，则需要对其转义。
例如： "Dear Jack\," "Dear Jack\\"

默认值： '' （空列表）
majorSeparators：主要分隔符列表。用引号引起来且之间没有空格。

默认值： E' []<>(){}|!;,''"*&?+\r\n\t'
minorSeparators：二级分隔符列表。用引号引起来且之间没有空格。

默认值： E'/:=@.-$#%\\_'
minLength — 标记拥有的最小长度，Integer 类型。必须大于 0。

默认值： '2'
maxLength：标记拥有的最大长度。Integer 类型。不得大于 1024 字节。有关增加标记大小的信息，请参阅文本搜索参数。

默认值： '128'
ngramsSize：大于 0 的整数值。仅与 ngram 分词器结合使用。

默认值： '3'
used：指示无法更改分词器配置。Boolean 类型。将 used 设置为 True 后，对 setTokenizerParameter 的任何调用将失败。

在使用已配置的分词器之前，您必须将参数 used 设置为 True。这样做可防止配置在用于创建文本索引后被修改。

默认值： False

parameter_value

配置参数的值。

如果您要禁用 minorSeperators 或 stopWordsCaseInsensitive，则将其值设置为 ''。

proc_oid

在创建分词器时为其分配的唯一标识符。用户必须查询系统表 vs_procedures 以为给定分词器名称获取 proc_oid。有关详细信息，请参阅配置分词器。

示例

以下示例显示了如何使用 SET_TOKENIZER_PARAMETER 配置非索引字和分隔符。

配置分词器的非索引字：

=> SELECT v_txtindex.SET_TOKENIZER_PARAMETER('stopwordsCaseInsensitive', 'devil,TODAY,the,fox' USING PARAMETERS proc_oid='45035996274126984');
 SET_TOKENIZER_PARAMETER
-------------------------
 t
(1 row)

配置分词器的主要分隔符：

=> SELECT v_txtindex.SET_TOKENIZER_PARAMETER('majorSeparators',E'{}()&[]' USING PARAMETERS proc_oid='45035996274126984');
 SET_TOKENIZER_PARAMETER
-------------------------
 t
(1 row)