Extend the code context in database queries [SQL]

[pierre-pedrito]

Hello,
I'm looking for a solution in order to extend the code context with the SQL. In other words, by code context I mean the sentences that precede and follow the coded text in the source text file.
So the key question in this topic is whether we can use the pos0 and pos1 attributes (or others attributes) to display the sentences before and after the coded text? It's not a question of displaying the whole source file, but just of increasing the number of characters (like 300 characters) to be displayed around the coded text.
I hope i'm clear.
Thank you.

[pierre-pedrito]

Update:

a (temporary) solution has been found using the substring function (variable, start, string length (pos1-pos0)+100) in the select part.

If you have another solution, I'd be happy to read it.

[ccbogel]

Hi, In Reports > Coding Reports you can tick the Text Context box to perform this function. In Project > Settings you can set how many characters before and after.
In SQL try this, which will show the preceding 100 characters and the postceding 100 characters:

select code_text.cid, substr(source.fulltext, iif(code_text.pos0+1-100 < 1,1,code_text.pos0+1-100), code_text.pos1 - code_text.pos0+1 +200) as "Extended", substr(source.fulltext, code_text.pos0+1, code_text.pos1 - code_text.pos0+1) as "Coded Text" from code_text join source on code_text.fid = source.id

You should add 1 as sqlite starts the string at the position of 1, and not 0 as python does. ( I added the 1 on separately just for understanding).
The iif() statement is needed, because you want the lowest string postions to be 1 and not a negative number.
If the string start position is a negative number, it then cycles back through the string.
https://www.sqlitetutorial.net/sqlite-functions/sqlite-substr/