CODEWITHSUNDEEP
python-3.xsqlite
snazzybouche
snazzybouche

Reputation: 2424

Using a user-defined function in a WHERE clause in sqlite3

Using the sqlite3 library in Python 3, I'm looking to filter a column by whether or not it matches a regex.

I found a question here that seems to do exactly that: Problem with regexp python and sqlite

The function in the top answer there takes arguments expr, item, where expr is given in the parametrized value and item is the cell given from iterating over the column bar. I can't find any documentation that would suggest that the value of the cell in the WHERE clause is passed into the last parameter of the custom function - it seems that it should be WHERE REGEXP(?,bar)?

Is there anywhere that documents this interaction explicitly?

Upvotes: 0

Views: 310

Answers (1)

Shawn
Shawn

Reputation: 52579

When you use x REGEXP y in sqlite, it's turned into regexp(y, x) if a function by that name exists, and an error otherwise. (LIKE and GLOB are treated the same way, except those are provided by sqlite instead of relying on an extension module or user defined function).

From the documentation:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

Upvotes: 1

Related Questions