Article 6KX3C How to get an /exact/ word match in SQLite3's FTS5?

How to get an /exact/ word match in SQLite3's FTS5?

by
Turbocapitalist
from LinuxQuestions.org on (#6KX3C)
I'm looking at the FTS5 full text searching module for SQLite3 and have a question about how to limit the search to an exact string.

If I have the following quick database:

Code:PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE metadata(recno integer, term varchar(25) not null, value varchar(256) not null);
INSERT INTO metadata VALUES(1,'dc.title','a foo/bar baz b');
INSERT INTO metadata VALUES(2,'dc.title','a bar foo/baz b');
INSERT INTO metadata VALUES(3,'dc.title','a barbar foo b');
INSERT INTO metadata VALUES(4,'dc.title','a foofo bar b');
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','text','text',0,'CREATE VIRTUAL TABLE text USING FTS5( m, tokenize="trigram case_sensitive 1")');
CREATE TABLE IF NOT EXISTS 'text_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO text_data VALUES(1,X'0431');
INSERT INTO text_data VALUES(10,X'000000000101010001010101');
INSERT INTO text_data VALUES(137438953473,X'000000ee043020626101020b0102030102030102090202666f01020301020701020a01020301032f626101020701020b01036120620202020102020301660102020302020202722001020901020501020801020b03016203020502027a2001020d01020d01036261720102080102040104040501020a03017a01020c01020c0103666f2004020703016f01020401020801020b01020401036f206203020d01020802022f6201020601020a0202666f04020602026f2003020c03012f010205010209030166040205010372206201020a03020c0301660202060102090202626103020601037a206201020e01020e0411100b0b0910060a1209080f0b0a070709060b0907');
CREATE TABLE IF NOT EXISTS 'text_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
INSERT INTO text_idx VALUES(1,X'',2);
CREATE TABLE IF NOT EXISTS 'text_content'(id INTEGER PRIMARY KEY, c0);
INSERT INTO text_content VALUES(1,'a foo/bar baz b');
INSERT INTO text_content VALUES(2,'a bar foo/baz b');
INSERT INTO text_content VALUES(3,'a barbar foo b');
INSERT INTO text_content VALUES(4,'a foofo bar b');
CREATE TABLE IF NOT EXISTS 'text_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
INSERT INTO text_docsize VALUES(1,X'0d');
INSERT INTO text_docsize VALUES(2,X'0d');
INSERT INTO text_docsize VALUES(3,X'0c');
INSERT INTO text_docsize VALUES(4,X'0b');
CREATE TABLE IF NOT EXISTS 'text_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO text_config VALUES('version',4);
PRAGMA writable_schema=OFF;
COMMIT;It create two tables. One has four rows, the other is the FTS representation of those four rows. If I I provide the following query,

Code:sqlite> SELECT rowid,m FROM text WHERE m MATCH ('foof');
4|a foofo bar bthe database returns partial matches instead of an empty set. The string 'foof', as a whole word, does not occur anywhere. What do I need to set so I can search for exact word matches and not have it glob either end automatially?
External Content
Source RSS or Atom Feed
Feed Location https://feeds.feedburner.com/linuxquestions/latest
Feed Title LinuxQuestions.org
Feed Link https://www.linuxquestions.org/questions/
Reply 0 comments