CODEWITHSUNDEEP
regexsqlite
raylight
raylight

Reputation: 727

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

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?

Upvotes: 1

Views: 1180

Answers (1)

forpas
forpas

Reputation: 164089

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)

Upvotes: 1

Related Questions