CODEWITHSUNDEEP
oracle-databaseoracle-text
E. L.
E. L.

Reputation: 522

Bound Oracle Text "near" operator to the same sentences

I have a column that stores paragraphs with multiple sentences and I am using the "Near" statement to look for the right record. However, is it possible to bound the near statement that only look for words within the same sentences.

For example:

Paragraph

"An elderly man has died as a result of coronavirus in the Royal Hobart Hospital overnight. It follows the death of a woman in her 80s in the North West Regional Hospital in Burnie on Monday morning, and brings the national toll to 19. "

indextype is ctxsys.context

select score(1) from tbl where contains(Paragraph, 'Near (coronavirus, death),20,false)',1) > 0

The result I want is nothing as both words is from a different sentences. However, now it will return me a positive number as both words are less than 20 words apart.

Can you share me some idea on how to do this?

Thanks in advance!

Upvotes: 0

Views: 298

Answers (1)

user5683823
user5683823

Reputation:

The query should look like this:

select score(1) 
from   tbl
where  contains(Paragraph, 'Near (coronavirus, death),20,false) 
                            WITHIN SENTENCE',1) > 0
;

That is - use the WITHIN operator.

Note that you must tell the index to recognize sentences first. That is: if you created the index with a statement like this:

create index ctxidx on tbl(Paragraph)
indextype is ctxsys.context
-- parameters(' ... ')
;

where the parameters (if you used that clause) don't say anything about "sentences", you will get an error if you try the query above - something along the lines of

DRG-10837: section sentence does not exist

First you will have to define "special" sections for sentences:

begin 
  ctx_ddl.create_section_group('my_section_group', 'AUTO_SECTION_GROUP');
  ctx_ddl.add_special_section('my_section_group', 'SENTENCE');
end;
/

With this in hand:

drop index ctxidx;

create index ctxidx on tbl(Paragraph)
indextype is ctxsys.context
parameters ('section group my_section_group')
;

Now you are ready to successfully run the query at the top of this Answer.

Upvotes: 2

Related Questions