I have a database filled with pure texts. I want a query to find some similar words to a specific sub-word. something like suggesting or guessing.
this is a database filled with data.
The sub-word I am looking for is da.
Now the result of the query should be only a column of single words like:
database
data
First you need to find rows that match 'da'. You could use something like LIKE '%da%' or REGEXP 'da' (see this SO answer to know how to install REGEXP extension).
Then you should extract the word corresponding to your pattern 'da'. That's where SQLite falls short of direct solution. In other DBMS, you have functions like substring(string from pattern) (for example in PostgreSQL). You don't have that kind of function in SQLite.
You could develop such a function on the same basis as functions in this extension-functions.c file in the contribs or this regexp operator for SQLite. But here you don't need the operator but the function that returns the match. I haven't found an existing extension for that.
Maybe you could use SQLite Full-Text Search extension tokenizers to extract the words you want.
