Hello, I have a novice question concerning searching for words and word combinations within strings of text.
The table includes translation_no, book_no, chapter_no, verse_no, text. I'd like to be able to return all rows in which, for example, a variable number of specific words occur in any order in the text of a particular translation; and I'd like to return the results in order of all verses that have the words in exact order, then the words sequentially in any order, and, after that, it really doesn't matter much apart from being in book_no, chapter_no, verse_no order.
I assume it would be very inefficient to search through the text of every row in this table for each request. The text column ranges in length from 11 to 556 characters.
Should I build a table that is the equivalent of an electronic concordance, where there is one row for each unique word that occurs across all rows of text, and a list of every combination of book_no, chapter_no, verse_no, word_no; and then examine the intersection of the three in non-SQL code (currently using Tcl)?
Or, have multiple rows for every unique word with its book_no,..., word_no values, and determine the result from within SQLite? Perhaps, a CTE that returns a table for each individual word, and then select all rows that have the same book_no, chapter_no, verse_no as a start?
Or is there a better approach? Thank you for any guidance you may be able to provide.