3

I'm programming for the Android, using SQLITE's FTS3 tables (virtual tables). Is there any way to have a query return null values in the same query one uses the MATCH statement?

As an example, i have tried the following:

Select rowid, Category From 'MyTable' Where Category IS NULL OR Category Match 'Term1 OR Term2 OR Term3 -Term4'

I got a run time error; apparently I can't combine them.

I have also tried including various match inputs like '', "", ''''', """"", 0, NULL, etc to try to get the match function to include Null values. Can't find any documentation if there is a special parameter to include.

Any ideas?

sean262 none
  • 212
  • 2
  • 8
  • What runtime error are you getting? – Joachim Isaksson Jun 26 '13 at 07:42
  • 1. unable to use function MATCH in the requested context, db=xxx 2. exception: SQL logic error or missing database; – sean262 none Jun 26 '13 at 08:01
  • 1
    Hm, silly double check, are you sure the version you're running has FTS3 enabled? Does it work without the null check? – Joachim Isaksson Jun 26 '13 at 08:54
  • Works for me. Check the output of `pragma compile_options;` for `ENABLE_FTS3`. – CL. Jun 26 '13 at 09:11
  • I only intended to show it as something that doesn't work, as to why i'm looking for a workaround. Several other questions [(link here)](http://stackoverflow.com/questions/11764357/a-limitation-of-sqlite3s-full-text-search-doesnt-allow-ors-with-matches-worka) indicate that MATCH cannot work with an OR operator outside of the MATCH statement. Not sure how you got it to work? If its because of the pragma settings, not sure it will help me because I'm not compiling SQLite but using the built-in version for android for my app. – sean262 none Jun 26 '13 at 10:13
  • 1
    Okay figured out a workaround by looking at the answer [here] (http://stackoverflow.com/questions/2621308/sqlite-fts3-search-for-a-string-in-a-column). You can combine two select statements with a union, e.g. SELECT rowid, category FROM MyTable WHERE category IS NULL UNION SELECT rowid, category FROM MyTable WHERE category MATCH 'term1 OR term2 OR term3 -Term4' If we could find the nulls as one of the terms in the Match statement, that would be the most efficient, but haven't found a way to do that yet. – sean262 none Jun 26 '13 at 10:42

0 Answers0