Also one other question: with this method of using a parameter in the MATCH expression, do I still need to manually escape double quotes in the string bound to the parameter by replacing them with a pair of double quotes (for FTS5)? I'm not seeing any errors, but now I'm second guessing myself.
Ben On Wed, May 23, 2018 at 10:46 AM, Ben Asher <benashe...@gmail.com> wrote: > Ah I see. Clever! The final solution ended up being: > > SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*' > > with || on either side of the ?. Does that sound right? Without that extra > ||, sqlite returned a syntax error (near ?). > > As follow up, it'd be great to see this solution documented somewhere on > the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to > accomplish this, but the solution is a bit more clever than I would have > come up with on my own. > > Thanks again for your help! > > Ben > > On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy <danielk1...@gmail.com> > wrote: > >> On 05/23/2018 06:02 AM, Ben Asher wrote: >> >>> Hi there! I'm working with a FTS5 query like this: >>> >>> SELECT rowid FROM text_fts_index WHERE text MATCH ? >>> >>> The parameter is something like this: "user input"* >>> >>> The idea here is to do a MATCH prefix-type query in FTS5. The problem >>> with >>> this query is that it breaks if the user input contains double quotes. >>> What >>> I want to do is something like this: >>> >>> SELECT rowid FROM text_fts_index WHERE text MATCH "?"* >>> >> >> Maybe: >> >> SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*' >> >> Dan. >> >> >>> This 2nd version of the query doesn't appear to work though because the ? >>> is escaped in the quotes and doesn't appear to be recognized as a >>> variable. >>> Is there a way I'm missing to do this kind of MATCH prefix query and only >>> escape the user input? >>> >>> Thanks! >>> >>> Ben >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Ben > -- Ben _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
