1

I am trying to query a Sqlite database taking into account 2 fields, say title and author, with the following query:

SELECT * FROM books WHERE title MATCH 'Author:<author>* <title>*'

It works when <author> and <title> do not contain spaces (i.e. a single word), but when I try, for example, for author: "mark tw", it will not match any rows because there are 2 words.

So, is it possible to use a prefix for a phrase query composed of multiple words?

niculare
  • 3,629
  • 1
  • 25
  • 39

2 Answers2

1

A search with a column specification should be done against the entire table, and a phrase search is not parsed correctly in this situation if there is no space after the colon:

... WHERE books MATCH 'Author: "mark tw*" title*'
CL.
  • 173,858
  • 17
  • 217
  • 259
  • I tried this and it does not work for me. It gives no results, although I have some entries which match the criteria. – niculare Jan 29 '15 at 05:08
  • Fixed the left operand of MATCH. – CL. Jan 29 '15 at 08:23
  • Still doesn't work. Have you tried it and works for you? I suppose that FTS does not support phrase query with token prefix... But maybe there's still a solution for this usecase. – niculare Jan 29 '15 at 09:32
  • Works for me. Show actual example data. – CL. Jan 29 '15 at 09:43
1

Can't work. A match on a specific column can't contain spaces at this moment. Something like match column1: "abc 123" just matches any column with "abc 123" in it because the query parser does not see "abc 123" as an argument to column1, but as a different filter (it sees it like column1:anything "abc 123"). Seems like an oversight to me. column1:"abc 123" (with no space) does not return anything. column1:abc 123 does return rows where column1 matches abc and one of the other columns contains 123.

Rinzwind
  • 31
  • 1