SQLite FTS, how to return only matching line in SQL?

Question

I have a virtual full text search table in SQLite. I can query it like this.

select body from my_table where body match 'foo';

This works great to return an entire row that matches 'foo'.

However, my problem is that the body column sometimes contains several paragraphs. I don't always need or want all of the paragraphs. Actually, I'm trying to return only the line the matched.

So instead of this:

sqlite> select body from my_table where body match 'adipiscing';
Lorem ipsum dolor sit amet.
Consectetur adipiscing elit.
Fusce in ipsum lacinia.

I just want this:

sqlite> select idk(body) from my_table where body match 'adipiscing';
Consectetur adipiscing elit.

I know about the highlight function, but that's not the behavior I want. I want to truncate the text around the match.

Zanda · Accepted Answer · 2025-04-15 04:34:19Z

0

There is no build in function to do this easily as per my experience, But there is a workaround which can be a performance drop depending on the data in the body and the record count

WITH RECURSIVE
  split_paragraphs AS (
    SELECT
      id,
      SUBSTR(body, 1, INSTR(body || '\n\n', '\n\n') - 1) AS paragraph,
      SUBSTR(body, INSTR(body || '\n\n', '\n\n') + 2) AS remaining
    FROM my_table
    WHERE body LIKE '%adipiscing%'
    
    UNION ALL
    
    SELECT
      id,
      SUBSTR(remaining, 1, INSTR(remaining || '\n\n', '\n\n') - 1),
      SUBSTR(remaining, INSTR(remaining || '\n\n', '\n\n') + 2)
    FROM split_paragraphs
    WHERE remaining <> ''
  )
SELECT paragraph
FROM split_paragraphs
WHERE paragraph LIKE '%adipiscing%'
LIMIT 1;

answered Apr 15 at 4:34

Zanda

11210 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite FTS, how to return only matching line in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related