Search code examples
regexmonetdb

monetdb regexp select

I'm doing some testing with MonetDB. The gist of the query I'm trying perform (using borrowed syntax) goes like this: SELECT mystring FROM mytable WHERE mystring REGEXP 'myxpression';

MonetDB does not support this syntax, but the docs claim that it supports PCRE, so this may be possible, still the syntax eludes me.

Solution

  • Check the Does MonetDB support regular expression predicates?

    The implementation is there in the MonetDB backend, the module that implements it is pcre (to be found in MonetDB5 source tree). I'm not sure whether it is available by default from MonetDB/SQL.

    If not, with these two function definition, you link SQL functions to the respective implementations in MonetDB5:

    -- case sensitive create function pcre_match(s string, pattern string) returns BOOLEAN external name pcre.match;

    -- case insensitive create function pcre_imatch(s string, pattern string) returns BOOLEAN external name pcre.imatch;

    If you need more, I'd suggest to have a look at MonetDB5/src/modules/mal/ pcre.mx in the source code.

    Use select name from sys.functions; to check if the function exists, otherwise you will need to create it.

    As an example, you may use pcre_imatch() like this:

    SELECT mystring FROM mytable WHERE pcre_imatch(mystring, 'myexpression');