DB2中实现正则表达式



正则表达式用于查找和替换字符串中的模式。正则表达式是用某种语法定义的，正则表达式引擎采用这种语法并将它与字符串进行比较。引擎返回字符串是否与语法匹配的指示；也即，该字符串是否包含能够从该语法派生的子串。此外，引擎还能够返回匹配的子串。术语“模式（pattern）”用来表示语法。

　　最基本的模式仅由单个字母组成。当与该模式进行比较时，包含这个字母的字符串就是一个“匹配”。例如，如果模式是“a”，则字符串“abcd”就是一个匹配，而字符串“xyz”则不是。正则表达式的强大功能来自于预定义的运算符（也称为元字符），它们可以用很小的空间来表示模式。根据“方言”和受支持的功能，可以使用不同的元字符。通常，其中的一些可用字符如下：

| — 二中择一 [ ] — 分组 * — 多次出现（也匹配零次出现） + — 多次出现（至少一次） ? — 随意的出现次数 \\\\ — 反斜杠

　　不同的系统实现了常用正则表达式的各种扩展。编程语言 Perl 中使用的正则表达式支持进一步的缩写。本文中所用的库实现了这些扩展。下面摘录了其中部分可以在 Perl 正则表达式语言中使用的缩写：

\\s — 任意空白字符 \\w — 任意字母数字字符 \\d — 任意数字字符

　　另一个更高级的示例是模式“[A-Z]* = ([0-9]|0x00);”。与这个模式相匹配的字符串包含这样的子串：它由几个大写字母、后面跟上一个空格、一个等号、另一个空格，然后是一个数字或字符串“0x00”组成。该子串的最后一个字符必须是分号。使用 Perl，这个模式可以表示为“\\w* = (\\d|0x00);”。“NM = 0x00;”和“X = 7;”是两个可以与该模式匹配的字符串。但字符串“Z = 123;”不能匹配，因为 123 是由三个数字所组成的。

　　DB2 中的字符串匹配

　　除了 Extender 以外，DB2 还允许几种用于文本比较的函数和运算符。但那些函数和运算符要么在用于模式匹配的功能方面有限制，要么就是会给可能使用它们的查询带来复杂性。这里简要地摘录几个可用的功能：

= 或 <> 谓词：逐字符地比较两个字符串是否相等。
LIKE 谓词：使用通配符的基本模式匹配。
LOCATE 函数：在字符串中查找子串。

　　尽管也可以用 SQL 运算符表示模式“[A-Z]* = ([0-9]|0x00);”，但那样会很麻烦。例如，下列 SELECT 语句的 WHERE 子句中所使用的谓词会匹配字符串“str”中等号之后的部分，如 清单 1所示：

清单 1. 使用 LIKE 匹配模式

SELECT str FROM strTable WHERE ( str LIKE '% = 0;%' OR str LIKE '% = 1;%' OR str LIKE '% = 2;%' OR str LIKE '% = 3;%' OR str LIKE '% = 4;%' OR str LIKE '% = 5;%' OR str LIKE '% = 7;%' OR str LIKE '% = 7;%' OR str LIKE '% = 8;%' OR str LIKE '% = 9;%' OR str LIKE '% = 0x00;%' )

　　这增加了可以匹配“[A-Z]*”子模式的谓词的复杂度，这可以使用对整个字符串进行迭代并进行逐字符比较的函数来完成，但您会发现使用内置功能既冗长又复杂。

　　示例方案

　　让我们定义下列清单（ 清单 2）并插入几行：

清单 2. 创建我们的样本表

CREATE TABLE strTable ( c1 INTEGER, str VARCHAR(500) ); INSERT INTO strTable VALUES ( 1, 'some text;' ), ( 2, 'variable = 1234;' ), ( 3, 'var2 = ''string variable'';' ), ( 4, 'xyz = ' ), ( 5, 'myVar = 0x00;' ), ( 6, '# comment' ), ( 7, 'abc = def' );

　　这个 清单及其数据被用于下面的所有示例。

SELECT * FROM strTable; C1 STR ----------- ------------------------------ 1 some text; 2 variable = 1234; 3 var2 = 'string variable'; 4 xyz = 5 myVar = 0x00; 6 # comment 7 abc = def 7 record(s) selected.

　　实现模式匹配函数

　　您可以使用 DB2 的可扩展机制，在 SQL 语句内使用 UDF，以便显著地改善这种情形。通过定义名为 regex1 的 UDF（它采用模式和字符串作为输入参数）， 清单 1中的 WHERE 子句现在可以写得象 清单 3中所示的那样：

清单 3. 使用 regex UDF 来简化模式匹配

SELECT str FROM strTable WHERE regex1('\\w* = (\\d|0x00);', str) = 1

　　在本示例中，使用带有 Perl 扩展的正则表达式来匹配完整的模式，而不仅仅是 清单 1中给出的 LIKE 谓词所对应的部分模式。正如您所看到的，使用函数来为该模式编写谓词比用 LIKE 谓词表示同样的语义要容易得多。

　　实现 UDF

　　在我的示例实现中，我选择了现有的名为 PCRE（Perl 兼容的正则表达式，Perl-compatible regular expression）的模式匹配引擎。该引擎提供了用来处理模式和执行匹配的 C API。该引擎和查询中所用的 SQL 语言之间“缺失的部分”是 UDF。该 UDF 由两部分组成：

• 在数据库中创建（或注册）该函数的 CREATE FUNCTION 语句。
• 该函数的主体，它实现了用于正则表达式匹配引擎的 C API 调用的封装器

　　清单 4显示了用于创建该函数的 SQL 语句。

清单 4. 注册 regex1 函数

CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M)) RETURNS INTEGER SPECIFIC regexSimple EXTERNAL NAME 'regexUdf!regexpSimple' LANGUAGE C PARAMETER STYLE DB2SQL DETERMINISTIC NOT FENCED RETURNS NULL ON NULL INPUT NO SQL NO EXTERNAL ACTION ALLOW PARALLEL;

　　注：请参阅 DB2 SQL Reference以获取所有子句的详细含义。可以修改参数的长度以适应您的需求。我在此处展示某些值并没有任何推荐使用它们的用意。

　　第二部分由一小段 C 代码组成，它实现了 UDF 入口点。在查询执行期间，DB2 为每个要与模式匹配的行调用这个入口点。 清单 5中的示例列出了该代码的清单。有关 pcre_* 函数和宏的描述，请参考 PCRE 库的文档。有关 C 代码的编译和共享库的构建，请参考 DB2 Application Development Guide。

　　清单 5. 实现 rege1x UDF 入口点的 C 代码

#include #include void regexpSimple( // input parameters SQLUDF_VARCHAR *pattern, SQLUDF_CLOB *str, // output SQLUDF_INTEGER *match, // null indicators SQLUDF_NULLIND *pattern_ind, SQLUDF_NULLIND *str_ind, SQLUDF_NULLIND *match_ind, SQLUDF_TRAIL_ARGS) { pcre *re = NULL; const char *error = NULL; int errOffset = 0; int rc = 0; // we assume successful return *match_ind = 0; // compile the pattern to its internal representation re = pcre_compile(pattern, 0 /* default options */, &error, &errOffset, NULL); if (re == NULL) { snprintf(SQLUDF_MSGTX, 70, "Regexp compilation failed at " "offset %d: %s\\n", errOffset, error); strcpy(SQLUDF_STATE, "38900"); (*pcre_free)(re); return; } // match the string againts the pattern rc = pcre_exec(re, NULL, str->data, str->length, 0, 0 /* default options */, NULL, 0); switch (rc) { case PCRE_ERROR_NOMATCH: *match = 0; break; case PCRE_ERROR_BADOPTION: snprintf(SQLUDF_MSGTX, 70, "An unrecognized bit was set in the " "options argument"); strcpy(SQLUDF_STATE, "38901"); break; case PCRE_ERROR_NOMEMORY: snprintf(SQLUDF_MSGTX, 70, "Not enough memory available."); strcpy(SQLUDF_STATE, "38902"); break; default: if (rc < 0) { snprintf(SQLUDF_MSGTX, 70, "A regexp match error " "occured: %d", rc); strcpy(SQLUDF_STATE, "38903"); } else { *match = 1; } break; } // cleanup (*pcre_free)(re); return; }

　　用法示例

　　下列查询试图从表 strTable 中找出包含注释文本的所有字符串。注释以“#”开头，所以模式是“#”后跟非空文本。

SELECT c1, str FROM strTable WHERE regex1('#\\s*\\w+', str) = 1;

　　结果只包含 c1 = 6 的行。

C1 STR ----------- ------------------------- 6 # comment; 1 record(s) selected.

　　在第二个示例中，我们试图找到这种赋值形式的字符串；即“text = text”。为了进一步缩小范围，我们只查找那些右端为数值的赋值。将十六进制表示法作为有效数值对待。

SELECT c1, str FROM strTable WHERE regex1('\\w+\\s*=\\s*(\\d+|0x\\d\\d)', str) = 1;

　　除了 c1 为 2 或 5 的两行以外，其它行都不包含数值的赋值，因此不会出现在结果中：

C1 STR ----------- ------------------------- 2 variable = 1234; 5 myVar = 0x00; 2 record(s) selected.

　　改进性能

　　尽管上面的函数按照预期的方式工作，但还可以改进它以获得更佳的性能。注：函数内部的执行完成得越快，DB2 处理整个 SQL 语句的速度也就越快。

　　SQL 旨在处理多组行，这意味着通常会针对一个模式匹配多个行。在大多数情况下，模式本身对于整个 SQL 语句都是不变的；即，它不会随行的更改而更改。 清单 5 中的 C 代码展示了对每一行都调用函数 pcre_compile() ，该函数将给定模式转换成内部表示法。

　　DB2 通过使用所谓的“高速暂存（scratchpad）”提供了在 UDF 调用之间传递信息的机制。此外，您可以标识特定调用“类型”；即它是对该 UDF 的第一次调用、普通调用还是最后一次（最终）调用。使用高速暂存和调用类型，有可能只对模式编译一次，然后将该已编译模式的内部表示法重用于对该 UDF 的所有后续调用。在最后一次调用时，释放在处理期间分配的资源。

　　如 清单 6所示，对 CREATE FUNCTION 语句进行修改，告诉 DB2 向外部 C 代码提供高速暂存和调用类型：

　　清单 6. 将高速暂存和调用类型添加到 CREATE FUNCTION 语句

CREATE FUNCTION regex2(pattern VARCHAR(2048), string CLOB(10M)) RETURNS INTEGER SPECIFIC regexPerf EXTERNAL NAME 'regexUdf!regexpPerf' LANGUAGE C PARAMETER STYLE DB2SQL DETERMINISTIC NOT FENCED RETURNS NULL ON NULL INPUT NO SQL NO EXTERNAL ACTION SCRATCHPAD 50 FINAL CALL ALLOW PARALLEL;

UDF 入口点看起来很不一样，因为必须改写函数内部的逻辑。参数方面唯一的更改是使用 SQLUDF_TRAIL_ARGS_ALL 代替了 SQLUDF_TRAIL_ARGS ，如 清单 7所示。

清单 7. regex2 的 C UDF 入口点

#include #include // data structure mapped on the scratchpad for easier use and access // to the objects // the size of the scratchpad defined in the CREATE FUNCTION statement // must be at least as large as sizeof(scratchPadMapping) struct scratchPadMapping { pcre *re; pcre_extra *extra; const char *error; int errOffset; }; void regexpPerf( // input parameters SQLUDF_VARCHAR *pattern, SQLUDF_CLOB *str, // output SQLUDF_INTEGER *match, // null indicators SQLUDF_NULLIND *pattern_ind, SQLUDF_NULLIND *str_ind, SQLUDF_NULLIND *match_ind, SQLUDF_TRAIL_ARGS_ALL) // SQLUDF_SCRAT & SQLUDF_CALLT { int rc = 0; struct scratchPadMapping *scratch = NULL; // map the buffer of the scratchpad and assume successful return scratch = (struct scratchPadMapping *)SQLUDF_SCRAT->data; *match_ind = 0; switch (SQLUDF_CALLT) { case SQLUDF_FIRST_CALL: // initialize data on the scratchpad scratch->re = NULL; scratch->extra = NULL; scratch->error = NULL; scratch->errOffset = 0; // compile the pattern (only in the FIRST call scratch->re = pcre_compile(pattern, 0 /* default options */, &scratch->error, &scratch->errOffset, NULL); if (scratch->re == NULL) { snprintf(SQLUDF_MSGTX, 70, "Regexp compilation failed at " "offset %d: %s\\n", scratch->errOffset, scratch->error); strcpy(SQLUDF_STATE, "38900"); rc = -1; break; } // further analyze the pattern (might return NULL) scratch->extra = pcre_study(scratch->re, 0 /* default options */, &scratch->error); /* fall through to NORMAL call because DB2 expects a result already in the FIRST call */ case SQLUDF_NORMAL_CALL: // match the current string rc = pcre_exec(scratch->re, scratch->extra, str->data, str->length, 0, 0 /* default options */, NULL, 0); switch (rc) { case PCRE_ERROR_NOMATCH: *match = 0; rc = 0; break; case PCRE_ERROR_BADOPTION: snprintf(SQLUDF_MSGTX, 70, "An unrecognized bit was set " "in the options argument"); strcpy(SQLUDF_STATE, "38901"); rc = -1; break; case PCRE_ERROR_NOMEMORY: snprintf(SQLUDF_MSGTX, 70, "Not enough memory available."); strcpy(SQLUDF_STATE, "38902"); rc = -1; break; default: if (rc < 0) { snprintf(SQLUDF_MSGTX, 70, "A regexp match error " "occured: %d", rc); strcpy(SQLUDF_STATE, "38903"); rc = -1; } else { *match = 1; rc = 0; } break; } break; } // cleanup in FINAL call, or if we encountered an error in // the FIRST call (DB2 will make a FINAL call if we encounter // an error in any NORMAL call) if (SQLUDF_CALLT == SQLUDF_FINAL_CALL || (SQLUDF_CALLT == SQLUDF_FIRST_CALL && rc < 0)) { (*pcre_free)(scratch->re); (*pcre_free)(scratch->extra); } return; }

　　为了进一步改进该函数的性能，我添加了对函数 pcre_study() 的调用，该函数是由模式匹配引擎提供的。该函数进一步分析了该模式，并将额外的信息存储在独立的结构中。然后，在实际的匹配期间使用这些额外的信息来加快处理速度。通过使用一个非常简单的模式和大约 4000 行的表，我获得了 5% 的执行时间的改善。当然，模式越复杂，差异将越显著。 #p#先前提到该实现假定模式在处理期间不会随行的不同而更改。当然，如果模式确实更改了，您可以进行少量的改写以再次编译一个模式。要这样做，有必要跟踪当前（已编译的）模式并在每次调用中将它与所提供的模式进行比较。也可以在高速暂存中维护当前模式。但必须将它复制到独立的缓冲区，并且不能通过指针模式直接引用它，因为这个指针或它所引用的数据可能会更改或变为无效。至于相应的代码更改，就当作练习留给读者了。

　　返回匹配子串

　　大多数模式匹配引擎提供了一种方法，返回与指定模式或其一部分相匹配的子串。如果想在 SQL 中使用这种能力，则必须使用不同的方法来实现匹配函数。给定的字符串可能包含不止一个匹配的子串。例如，当解析类似“abc = 123;”或“def = 'some text';”这样的字符串时，用户可能会希望检索由等号分隔的两个子串。您可以使用模式“\\w+\\s*=\\s*(\\d+|'[\\w\\s] *');”来表示适用于该字符串的语法规则。Perl 兼容的正则表达式允许您捕获等号两边的子串。最后，必须将要捕获的子串用括号括起来。我已经用该方式编写了第二个子串，但第一个子串不是这样编写的。用于该用途的最终模式是这样的：

(\\w+)\\s*=\\s*(\\d+|'[\\w\\s]*');

　　当把这个模式应用于字符串“abc= 123;”或“def = 'some text';”时，“abc”或“def”分别与“(\\w+)”匹配，空格和等号是通过“\\s*=\\s*”查找的，并用另外的“(\\d+|'[\ \w\\s*]')”涵盖了余下的子串。在“(\\d+|'[\\w\\s*]')”中，第一个选项与任何至少由一个数字“\\d+”组成的数匹配，而第二个选项解析任何由字母和空格组成的由单引号括起的字符串“'[\\w\\s]*'”。

　　在 DB2 中做到这一点的需求可以描述成：为一次 UDF 调用返回多个结果。换句话说，就是返回针对模式进行匹配的单个字符串的多个子串。DB2 的表函数是完成这一任务的完美工具。

　　实现表 UDF

　　和以前一样，必须在数据库中创建该函数。 清单 8中的下列语句正是用于这一任务的：

清单 8. 注册名为 regex3 的表 UDF

CREATE FUNCTION regex3(pattern VARCHAR(2048), string CLOB(10M)) RETURNS TABLE ( position INTEGER, substring VARCHAR(2048) ) SPECIFIC regexSubstr EXTERNAL NAME 'regexUdf!regexpSubstr' LANGUAGE C PARAMETER STYLE DB2SQL DETERMINISTIC NOT FENCED RETURNS NULL ON NULL INPUT NO SQL NO EXTERNAL ACTION SCRATCHPAD 50 NO FINAL CALL DISALLOW PARALLEL;

　　实现该函数的实际逻辑的 C 代码与 清单 7中的代码非常相似，但根据表函数所必须满足的特殊需求对它进行了改编，如 清单 9所示。

　　清单 9. 实现表函数将要使用的 regex3 函数

#include #include #include #include struct scratchPadMapping { pcre *re; pcre_extra *extra; const char *error; int errOffset; int numSubstr; int *substr; int currentSubstr; }; void regexpSubstr( // input parameters SQLUDF_VARCHAR *pattern, SQLUDF_CLOB *str, // output SQLUDF_INTEGER *pos, SQLUDF_VARCHAR *substr, // null indicators SQLUDF_NULLIND *pattern_ind, SQLUDF_NULLIND *str_ind, SQLUDF_NULLIND *pos_ind, SQLUDF_NULLIND *substr_ind, SQLUDF_TRAIL_ARGS_ALL) // SQLUDF_SCRAT & SQLUDF_CALLT { int rc = 0; size_t length = 0; struct scratchPadMapping *scratch = NULL; // map the buffer of the scratchpad and assume NULL return scratch = (struct scratchPadMapping *)SQLUDF_SCRAT->data; *pos_ind = 0; *substr_ind = 0; switch (SQLUDF_CALLT) { case SQLUDF_TF_OPEN: // initialize data on the scratchpad scratch->re = NULL; scratch->extra = NULL; scratch->error = NULL; scratch->errOffset = 0; scratch->numSubstr = 0; scratch->substr = NULL; scratch->currentSubstr = 1; // skip the complete match // compile the pattern (only in the FIRST call scratch->re = pcre_compile(pattern, 0 /* default options */, &scratch->error, &scratch->errOffset, NULL); if (scratch->re == NULL) { snprintf(SQLUDF_MSGTX, 70, "Regexp compilation failed at " "offset %d: %s\\n", scratch->errOffset, scratch->error); strcpy(SQLUDF_STATE, "38900"); rc = -1; break; } // further analyze the pattern (might return NULL) scratch->extra = pcre_study(scratch->re, 0 /* default options */, &scratch->error); // determine the number of capturing subpatterns rc = pcre_fullinfo(scratch->re, scratch->extra, PCRE_INFO_CAPTURECOUNT, &scratch->numSubstr); if (rc) { snprintf(SQLUDF_MSGTX, 70, "Could not retrieve info " "on pattern. (rc = %d)", rc); strcpy(SQLUDF_STATE, "38901"); rc = -1; break; } // allocate memory for the substring indices { int size = (scratch->numSubstr+1)*3; scratch->substr = (int *)malloc(size * sizeof(int)); if (!scratch->substr) { snprintf(SQLUDF_MSGTX, 70, "Could allocate memory for " "substring indices."); strcpy(SQLUDF_STATE, "38902"); rc = -1; break; } memset(scratch->substr, 0, size * sizeof(int)); // match the current string rc = pcre_exec(scratch->re, scratch->extra, str->data, str->length, 0, 0 /* default options */, scratch->substr, size); } switch (rc) { case PCRE_ERROR_BADOPTION: snprintf(SQLUDF_MSGTX, 70, "An unrecognized bit was set " "in the options argument"); strcpy(SQLUDF_STATE, "38903"); rc = -1; break; case PCRE_ERROR_NOMEMORY: snprintf(SQLUDF_MSGTX, 70, "Not enough memory available."); strcpy(SQLUDF_STATE, "38904"); rc = -1; break; case PCRE_ERROR_NOMATCH: scratch->currentSubstr = scratch->numSubstr + 1; rc = 0; break; default: if (rc < 0) { snprintf(SQLUDF_MSGTX, 70, "A regexp match error " "occured: %d", rc); strcpy(SQLUDF_STATE, "38905"); rc = -1; break; } } break; case SQLUDF_TF_FETCH: // skip capturing substrings without a match while (scratch->currentSubstr <= scratch->numSubstr && (scratch->substr[2*scratch->currentSubstr] < 0 || scratch->substr[2*scratch->currentSubstr+1] < 0)) { scratch->currentSubstr++; } // no more data to be returned if (scratch->currentSubstr > scratch->numSubstr) { strcpy(SQLUDF_STATE, SQL_NODATA_EXCEPTION); rc = 0; break; } // get the current substring *pos = scratch->currentSubstr; length = scratch->substr[2*scratch->currentSubstr+1] - scratch->substr[2*scratch->currentSubstr]; strncpy(substr, str->data + scratch->substr[2*scratch->currentSubstr], length); substr[length] = '\\0'; scratch->currentSubstr++; } // cleanup in CLOSE call, or if we encountered an error in // the OPEN call (DB2 will make a CLOSE call if we encounter // an error in any FETCH call) if (SQLUDF_CALLT == SQLUDF_TF_CLOSE || (SQLUDF_CALLT == SQLUDF_TF_OPEN && rc < 0)) { (*pcre_free)(scratch->re); (*pcre_free)(scratch->extra); free(scratch->substr); } return; }

　　正如我们对基本匹配函数所做的那样，您也可使用 FINAL CALL 定义表函数来改进性能。必须修改 C 代码以处理 SQLUDF_TF_FIRST 和 SQLUDF_TF_FINAL 调用。

　　用法示例

　　表函数可以在类似于如下所示的 SELECT 语句中使用：

SELECT c1, str, num, substr FROM strTable, TABLE ( regex3('(\\w+)\\s*=\\s*(\\d+|''[\\w\\s]*'');', str) ) AS sub(num, substr)

　　结果只包括拥有匹配模式的字符串。对于每个字符串，都在单独的行中显示所捕获的第一个和第二个子串。

C1 2 NUM 4 ----------- ------------------------------ ----------- ----------------------- 2 variable = 1234; 1 variable 2 variable = 1234; 2 1234 3 var2 = 'string variable'; 1 var2 3 var2 = 'string variable'; 2 'string variable' 4 record(s) selected.

　　下一个查询在结果集中用单独的列而不是单独的行返回这两个子串对。这样，用 SQL 语句进一步处理这些字符串以及它们的子串要容易些。该查询使用了公共表表达式（由关键字 WITH 表示）来确保在整个查询中只对每个字符串进行一次求值，而不是在中间表 s1 和 s2 所需的每次子选择中都进行一次求值。

WITH substrings(c, num, substr) AS ( SELECT c1, num, substr FROM strTable, TABLE ( regex3('(\\w+)\\s*=\\s*(\\d+|''[\\w\\s]*'');', str) ) AS sub(num, substr) ) SELECT t.c1, s1.substr AS variable, s2.substr AS value FROM strTable AS t JOIN substrings AS s1 ON ( t.c1 = s1.c ) JOIN substrings AS s2 ON ( t.c1 = s2.c ) WHERE s1.num = 1 AND s2.num = 2

　　这里使用了与前面查询中相同的模式。因此，可以从上面的表中派生出结果，但这一次，根据请求，这些 variable-value 对每个都位于单独的行中。

C1 VARIABLE VALUE ----------- ------------------------------ -------------------- 2 variable 1234 3 var2 'string variable' 2 record(s) selected.

　　结束语

　　UDF 可以用来以两种形式在 DB2 中提供正则表达式。在第一种形式中，通过将字符串与给定模式进行比较来完成基本匹配。第二种形式是实现表函数，它从正则表达式抽取已捕获的子串，并将这些子串返回给 DB2，以便用在 SQL 语句的进一步处理中。我还给出了一些关于如何改进性能的提示。