Forum Stats

  • 3,814,623 Users
  • 2,258,892 Discussions
  • 7,892,787 Comments

Discussions

How to search exact phrase, and how to use regex in Oracle Text?

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Aug 8, 2018 8:29AM in Text

Hi everyone,

I am a beginner user of Oracle Text, please be kind to me

I am trying to do search on exact phrase 'change management'. How should i construct the query to find items that exactly match those two words? I tried searching normally like this using CONTEXT index:

select *

from MY_TABLE

where contains(column_name, 'change management', 1) > 0

"Change blabla management" should not be returned, because there is another word in between.

"Change blabla" should not be returned because it is missing the second word.

"bla bla Change management bla bla" is a correct result.

The second question would be, how to use regex during search? For example, I want to search for a keyword "defer" which can be written as "defer" or "deferred". How should I construct the query to handle this?

Thank you in advance!

Tagged:

Best Answer

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Sep 14, 2017 2:00PM Answer ✓

    That query is how you search for exact terms and it works for me.

    drop table tab1 purge;
    create table tab1(col1 varchar2(100));

    insert into tab1 values('Change blabla management');
    insert into tab1 values('Change blabla');
    insert into tab1 values('bla bla Change management bla bla');
    commit;create index tab1_idx on tab1(col1) indextype is ctxsys.context;select *
    from tab1
    where contains(col1, 'change management', 1) > 0
    /

    I only get the one row back. that you wanted.

    >>For example, I want to search for a keyword "defer" which can be written as "defer" or "deferred". How should I construct the query to handle this?

    You can use Stemming:

    Stemming enables you to match words with the same linguistic root. For example a query on $speak, expands to search for all documents that contain speak, speaks, spoke, and spoken.

    http://docs.oracle.com/database/122/CCAPP/indexing-with-oracle-text.htm#GUID-098F287A-DD02-42E1-B685-B1E4ED108FBA

    or you can wildcards but that would likely return more false positives:

    where contains(col1,'defer%')>0

Answers

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Sep 14, 2017 2:00PM Answer ✓

    That query is how you search for exact terms and it works for me.

    drop table tab1 purge;
    create table tab1(col1 varchar2(100));

    insert into tab1 values('Change blabla management');
    insert into tab1 values('Change blabla');
    insert into tab1 values('bla bla Change management bla bla');
    commit;create index tab1_idx on tab1(col1) indextype is ctxsys.context;select *
    from tab1
    where contains(col1, 'change management', 1) > 0
    /

    I only get the one row back. that you wanted.

    >>For example, I want to search for a keyword "defer" which can be written as "defer" or "deferred". How should I construct the query to handle this?

    You can use Stemming:

    Stemming enables you to match words with the same linguistic root. For example a query on $speak, expands to search for all documents that contain speak, speaks, spoke, and spoken.

    http://docs.oracle.com/database/122/CCAPP/indexing-with-oracle-text.htm#GUID-098F287A-DD02-42E1-B685-B1E4ED108FBA

    or you can wildcards but that would likely return more false positives:

    where contains(col1,'defer%')>0

This discussion has been closed.