SQLite Forum

Use custom string function in FTS5 match clause
What I gather from your post is that the above queries are either failing to compile (when sqlite3_prepare() is called) or they compile but fail to yield the results you hope to see.  You have presented a lot of code to read, which indicates that you have not narrowed this problem much, if at all.

One problem that does appear from a superficial study is this: Your function, simple_query(), when given the SQL literal '''' as its argument, appears to return either the SQL empty string literal token (two successive single-quotes), or that token bounded by double-quotes. Neither one is likely to be a suitable right operand for the match operator.

I think maybe your problem is that you expect that a pair of concatenated single-quotes, as a **string value**, should be interpreted as a length 1 string value containing a single-quote character. That is not so. The conversion of a SQL literal such as <code>''''</code> to that length 1 string value happens when the SQL is parsed (or lexically analyzed, actually), and not as its parsed and compiled form is executed. Of course, the return from your custom function known as simple_query() is never passed through the SQL lexer or compiler; instead it is made, (by the compiler's output code), to participate in evaluation of the binary match expression with whatever string value it has, without any quoting, quote-escaping, or any such finagling.