0

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?

snazzybouche
  • 2,241
  • 3
  • 21
  • 51

1 Answers1

1

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)".

Shawn
  • 47,241
  • 3
  • 26
  • 60
  • Thank you! Does this mean that it's not possible to add completely custom functions say `WHERE x ELICITS y` or something similarly context-dependent? – snazzybouche Jun 17 '19 at 19:21
  • 1
    @snazzybouche It's not possible to add arbitrary new operators like that, no. At least not without going in and modifying the source. – Shawn Jun 17 '19 at 19:24
  • Good to know, thank you. Though I guess you could possibly define REGEXP to do whatever you wanted it to, so long as you only need one such function – snazzybouche Jun 17 '19 at 19:26
  • @snazzybouche `MATCH/match()` is another one I always forget. And you can have whatever you want for normal functions of course – Shawn Jun 17 '19 at 19:39