How can I search a regular expression with an inverted pattern using SQLite?

Question

I know that in order for using a regular expression with SQLite, I need to install a module for regular expressions as described on this answer.

However, even though the query

select * 
from mytable 
WHERE mycolumn is not NULL and mycolumn regexp('^Word.*$')

works fine, if I try to use:

select * 
from mytable 
WHERE mycolumn is not NULL and mycolumn is not regexp('^Word.*$')

I receive an error telling me:

wrong number of arguments to function regexp().

What am I missing? Is it a syntax error?

Or should I take a different approach to get the inverted pattern of a regular expression on SQLite?

forpas · Accepted Answer · 2021-06-29T20:05:32.740

In this statement:

... and mycolumn regexp('^Word.*$')

maybe you think that you are using the function regexp(), but actually you are using the regexp operator and the expression is interpreted as:

... and mycolumn REGEXP '^Word.*$'

So, the correct way to implement the inverted pattern is:

... and NOT (mycolumn REGEXP '^Word.*$')

or:

... and mycolumn NOT REGEXP '^Word.*$'

Note that you could use the regexp() function, but with arguments in the opposite order of the regexp operator:

... and regexp('^Word.*$', mycolumn)

and for the inverted pattern:

... and not regexp('^Word.*$', mycolumn)

1 Answers1