[sqlite] Querying nextchar extension

[Charles Leifer]

I was thinking of dynamically building up the strings using the `nextchar`
extension but your example works just fine.

On Fri, Sep 18, 2015 at 9:52 AM, Igor Tandetnik  wrote:

> On 9/18/2015 9:05 AM, Charles Leifer wrote:
>
>> As a challenge I was curious how one would write a recursive CTE to take a
>> substring and recursively calculate all possible matches. I realize I
>> could
>> just use 'LIKE xxx%' to accomplish the same, but if anyone has any
>> thoughts
>> on writing such a query I'd love to hear them!
>>
>
> Something along these lines (untested):
>
> select group_concat(substr(word, length(:prefix)+1, 1), '')
> from words where substr(word, 1, length(:prefix)) = :prefix
>
> Not sure what recursive CTE has to do with it - what is there to recurse
> over?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Querying nextchar extension

[Igor Tandetnik]

On 9/18/2015 9:05 AM, Charles Leifer wrote:
> As a challenge I was curious how one would write a recursive CTE to take a
> substring and recursively calculate all possible matches. I realize I could
> just use 'LIKE xxx%' to accomplish the same, but if anyone has any thoughts
> on writing such a query I'd love to hear them!

Something along these lines (untested):

select group_concat(substr(word, length(:prefix)+1, 1), '')
from words where substr(word, 1, length(:prefix)) = :prefix

Not sure what recursive CTE has to do with it - what is there to recurse 
over?
-- 
Igor Tandetnik

[sqlite] Querying nextchar extension

[Charles Leifer]

I've been playing around a bit with the `nextchar` extension and I must say
it's pretty neat! Basically I create a table named `words` with a single
TEXT column named `word` and populate it with a dictionary file. Then I can
run:

SELECT nextchar('partial word', 'words', 'word');

And it will return a string containing all the characters that could
possibly be the "next char" of a valid word.

For example:

nextchar('foota') -> 'g'
nextchar('footag') -> 'e'
nextchar('footage') -> no result

As a challenge I was curious how one would write a recursive CTE to take a
substring and recursively calculate all possible matches. I realize I could
just use 'LIKE xxx%' to accomplish the same, but if anyone has any thoughts
on writing such a query I'd love to hear them!