SQlite Full text search (FTS)?

Question

I have question about FTS on sqlite.

Create table :

CREATE VIRTUAL TABLE mail USING fts3(subject, body);

Insert demo data

INSERT INTO mail(docid, subject, body) 
VALUES(1, 'software feedback' 'found it too slow');

INSERT INTO mail(docid, subject, body) 
VALUES(2, 'software feedback', 'no feedback');

INSERT INTO mail(docid, subject, body) 
VALUES(3, 'slow lunch order',  'was a software problem');

Code for search query

SELECT * 
FROM mail 
WHERE subject MATCH 'software*';    ==> Selects rows 1 and 2

SELECT * FROM mail WHERE body MATCH 'feedback';    ==>  Selects row 2

SELECT * FROM mail WHERE mail MATCH 'software*';    ==> Selects rows 1, 2 and 3

SELECT * FROM mail WHERE mail MATCH 'slow';        ==> Selects rows 1 and 3

However, when I want to search text in a word. ex: I want to search any word contain character "o":

Ex:

SELECT * 
FROM mail 
WHERE mail MATCH 'o';

I get no results ...

Can anyone help me?

Answer 1

Have you considered using the LIKE operator? If you wanted any record from the mail table where the mail field had one or more o letters in it, you could try this:

SELECT * FROM mail WHERE mail LIKE '%o%';