DB2中如何实现正则表达式(1)

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

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

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;%' )

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.

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

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;

#include <pcre.h>
#include <sqludf.h>

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

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

C1          STR
----------- -------------------------
          6 # comment;

  1 record(s) selected.

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

C1          STR
----------- -------------------------
          2 variable = 1234;
          5 myVar = 0x00;

  2 record(s) selected.