Search code examples
sqlsqlitefull-text-searchmatch

How can you select entries in a FTS SQL db that only matches part of the query?


CREATE VIRTUAL TABLE TEST USING FTS5(SomeText);

INSERT INTO TEST(SomeText) VALUES 
('Covid 19');

SELECT SomeText 
FROM TEST 
WHERE SomeText MATCH 'sars covid 19'; 

=> no results

How could I make sure this also returns the column with 'Covid 19'? Is there an extension or function that considers this to keep the sql command simple?

fiddle: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=defbd331c8469e05ea6a107df10738a2


Solution

  • I think the syntax to be used in such case is 'Covid 19 OR sars'.

    For instance :

    CREATE VIRTUAL TABLE TEST USING FTS5(SomeText);
    
    INSERT INTO TEST(SomeText) VALUES 
    ('Sars'),
    ('Covid 19'),
    ('Covid 19 but also Sars'),
    ('Some other stuff');
    
    SELECT SomeText 
    FROM TEST 
    WHERE SomeText MATCH 'Covid 19 OR sars';
    

    Returns the following results :

    Sars
    Covid 19
    Covid 19 but also Sars
    

    You can check the following fiddle here

    See also : https://www.sqlitetutorial.net/sqlite-full-text-search/here