3 Replies Latest reply: Mar 7, 2014 6:23 AM by Roger Ford-Oracle RSS

    How to fulltext index more words - for example P A NC

    Martin Bikl

      Hi everybody,

       

      I need to fulltext index a special phrase, for example P A NC, B A KLP, PO A BZ, etc. White space is standard delimiter words.

      I need oposite to stopwords


      Could you help me

       

      Thanks

       

      Martin

        • 1. Re: How to fulltext index more words - for example P A NC
          Roger Ford-Oracle

          Not quite sure what you're looking for.  You can turn off stopwords by using "stoplist ctxsys.empty_stoplist" in the parameters clause.  But are you looking to index those phrases as single tokens?  That can't be done without pre-processing the text in some fashion, perhaps replacing the spaces with a special character and defining that as a PRINTJOINS character, so that "P A NC" is indexed as "P+A+NC" or similar.

          SQL> create table foo (bar varchar2(2000));
          
          Table created.
          
          SQL> insert into foo values ('the quick brown P A NC jumps over the lazy B A KLP');
          
          1 row created.
          
          SQL> update foo set bar = regexp_replace( bar, 'P A NC', 'P+A+NC');
          
          1 row updated.
          
          SQL> update foo set bar = regexp_replace( bar, 'B A KLP', 'B+A+KLP');
          
          1 row updated.
          
          SQL> select bar from foo;
          
          BAR
          --------------------------------------------------------------------------------
          the quick brown P+A+NC jumps over the lazy B+A+KLP
          
          SQL> exec ctx_ddl.create_preference('mylex', 'BASIC_LEXER')
          
          PL/SQL procedure successfully completed.
          
          SQL> exec ctx_ddl.add_attribute('mylex', 'PRINTJOINS', '+')
          BEGIN ctx_ddl.add_attribute('mylex', 'PRINTJOINS', '+'); END;
          
                        *
          ERROR at line 1:
          ORA-06550: line 1, column 15:
          PLS-00302: component 'ADD_ATTRIBUTE' must be declared
          ORA-06550: line 1, column 7:
          PL/SQL: Statement ignored
          
          
          SQL> exec ctx_ddl.set_attribute('mylex', 'PRINTJOINS', '+')
          
          PL/SQL procedure successfully completed.
          
          SQL> create index fooindex on foo(bar) indextype is ctxsys.context
            2  parameters ('lexer mylex stoplist ctxsys.empty_stoplist');
          
          Index created.
          
          SQL> select * from foo where contains (bar, 'B+A+KLP') > 0;
          
          BAR
          --------------------------------------------------------------------------------
          the quick brown P+A+NC jumps over the lazy B+A+KLP
          
          • 2. Re: How to fulltext index more words - for example P A NC
            Martin Bikl

            Hi Roger,

             

            your Idea is interesting.... It's a pity that doesn't exist better way......

             

            M.

            • 3. Re: How to fulltext index more words - for example P A NC
              Roger Ford-Oracle

              What is the problem you are trying to solve?  Can you not just search for the phrase "B A KLP"?