Sqlite FTS exact word

Question

SELECT * FROM my_table WHERE product MATCH "apple";

returns: apple, green apple, apple jam ... I need FTS query that returns only "apple". These work slow:

SELECT * FROM my_table WHERE product="apple";
SELECT * FROM my_table WHERE product LIKE "apple";

score 2 · Answer 1 · answered Jan 07 '15 at 10:56

2

With FTS tables, the only efficient queries are searches with MATCH.

With FTS4, you can restrict a search to the beginning of a document with ^:

SELECT * FROM my_table WHERE product MATCH '^apple';

However, this does not help for the end.

To do an efficient check for the entire column, do an FTS search first, then check the result of that:

SELECT *
FROM (SELECT product
      FROM my_table
      WHERE product MATCH '^apple')
WHERE product = 'apple';

answered Jan 07 '15 at 10:56

CL.

173,858
17
217
259

Works like a charm! A suggestion would be to use `SELECT * FROM my_table WHERE product MATCH '^apple'` as sub query to get all the columns which apparently is the requirement of OP. Thanks! +1 – Shahood ul Hassan Apr 06 '19 at 07:17
Oh, another suggestion: we have to check for the case as well in main query. So, the final query incorporating both the suggestions would be as follows: `SELECT * FROM (SELECT * FROM my_table WHERE product MATCH '^apple') WHERE LOWER(product) = 'apple';`. Hope it still helps someone even after 04 years since this answer was posted. – Shahood ul Hassan Apr 06 '19 at 07:49
Not sure if it's suppoorted everywhere, but the `\b` marker means "word boundary", so `\bapple\b` would do. – Michał Leon Feb 04 '20 at 09:30

score 0 · Answer 2 · answered Apr 30 '21 at 11:07

The purpose of FTS (full text search) is to find all exact and partial matches. When you search for "apple", it is expected to find apple, green apple, apple jam, apple11, apple123 (all the words starting with "apple" or phrases including the whole word "apple").

If you want to further narrow down the results, you could save the results in a temp table on the db side and search the temp table for exact match. Or you could also do this job on the client side:

DataTable dt = dbHelper.GetProducts(params);

string exactProductName = "apple";

// delete products from the table which are not an exact match
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
   DataRow dr = dt.Rows[i];
   if (dr["product"].ToString() != exactProductName )
      dr.Delete();
}

dt.AcceptChanges();
// now your data table includes only the exact match

score -2 · Answer 3 · answered Dec 19 '15 at 14:26

-2

change the sql like this

SELECT *
FROM my_table
WHERE product MATCH '^apple$'

this sql means find the terms which start with apple and end with apple.

answered Dec 19 '15 at 14:26

Jimmy Chen

177
5

Doesn't work! You can still get a match like 'apple pie'. – Shahood ul Hassan Apr 06 '19 at 07:14

Sqlite FTS exact word

3 Answers3