MonetDB正则表达式查询语法求助：REGEXP不支持但支持PCRE

Hey there, I’ve run into this exact regex quirk with MonetDB before—even though it supports PCRE, it doesn’t use the standard REGEXP keyword you’re trying. Let’s get your query working right away.

Correct Syntax Options #

MonetDB uses two straightforward alternatives to the REGEXP syntax:

1. Using the ~ operator (the most concise approach):
   Swap out your original query for this:

   SELECT mystring FROM mytable WHERE mystring ~ 'myxpression';
   

   The ~ operator directly checks if your string matches the PCRE pattern you provide.

2. Using the regexp_match() function:
   If you prefer an explicit function call, this works just as well:

   SELECT mystring FROM mytable WHERE regexp_match(mystring, 'myxpression') = true;
   

   This function returns a boolean value indicating whether the pattern matches the target string.

Bonus: Case-Insensitive Matching #

If you need to ignore case in your regex checks, you have two easy options:

• Use the ~* operator instead of ~:

  SELECT mystring FROM mytable WHERE mystring ~* 'myxpression';
  

• Add the PCRE case-insensitive modifier (?i) directly to your pattern:

  SELECT mystring FROM mytable WHERE mystring ~ '(?i)myxpression';
  

Both methods will match variations like MyXpression or MYXPRESSION without altering your core pattern.

内容的提问来源于stack exchange，提问作者airan