Search for the root words using root words with prefixes or suffixes

likeselectsqlite

Is it possible to search the word "FILL" saved in the SQLite database using the words "REFILL" or "FILLED"? Not just these words specifically but you get the idea of searching the root word using the same word but with prefixes or suffixes.

I might get misunderstood as wanting to use the LIKE operator/command. But what I need is somehow reverse of that. The case is that the root words (FILL) are the ones in my database and the words that I am going to use in the query are those with prefixes or suffixes (e.g REFILL, FILLED)

I cannot use those word-trimming API because it's not going to be done in English. A code for android/Sqlite would be great but the query command is already a great help. Thaanks.

Best Answer

What you're talking about is "stemming" where a word -- refill -- is related to its "root" or "stem" word -- fill. This is usually implemented through Full Text Search, which I believe SQLite supports, though I've never used it.

To implement this yourself in your own tables and code will be a complex task requiring a thorough understanding of the target language(s), how word-forms are constructed and how the grammar is defined, both theoretically and in practice.

Related Solutions

How to query for leaf nodes in a hierarchy using a closure table

I think this is what you want (edited):

-- ml : short name for middle-to-leaf connection
-- rm : short name for root-to-middle connection

SELECT                                     -- show me 
    leaf.*                                 -- all
FROM 
    as_node AS leaf                        -- leaf nodes
  JOIN as_node_closure AS ml               -- which have an ancestor
    ON  ml.descendant = leaf.id            -- 
  JOIN as_node AS middle                   -- (let's call it middle)
    ON  ml.ancestor = middle.id            -- 
    AND middle.name = 'trumpet'            -- with the name 'trumpet'
  JOIN as_node_closure AS rm               -- and this middle
    ON  rm.descendant = ml.ancestor        -- has another ancestor
  JOIN as_node AS root                     -- (which we call root)
    ON  rm.ancestor = root.id              -- which is indeed a root node
    AND root.parent IS NULL                -- as it has no parent
WHERE                                      -- and the distance from root to middle
      rm.depth = 2                         -- is 2 (so middle's absolute level is 2)
  AND leaf.name LIKE '%.wav'
  AND NOT EXISTS                           -- and also the leaf is indeed a leaf node
      ( SELECT *                           -- as it has no children
        FROM as_node AS extra
        WHERE extra.parent = leaf.id
      ) ;

Test at SQL-Fiddle (where 'FT' is used instead of 'trumpet', to match your data.)

Postgresql – What’s the most efficient way to perform a case insensitive search for a word in a text field

In PostgreSQL, you can use Text Search:

create index on foo_table using gin ( to_tsvector('simple', body) );

select 
*
from foo_table
where
  to_tsvector('simple', body) @@ plainto_tsquery('simple', 'word');

Best Answer

Related Solutions

How to query for leaf nodes in a hierarchy using a closure table

Postgresql – What’s the most efficient way to perform a case insensitive search for a word in a text field

Related Question