6 Replies Latest reply: Dec 8, 2011 12:14 PM by 876250 RSS

    Phrase Serach with special charaters in between

    876250
      Gurus,

      We were trying to implement a search functionality in which if we give two seperate words
      Mark Antony by default behaviour it will do phrase serach so only occurunces of these two words together will be returned.

      With blank space we also wanted to consider special characters in between these words like /,\,|,-,_,+

      so that the resultant records for querrying following phrases all should return the same results.

      Mark Antony
      Mark/Antony
      Mark\Antony
      Mark|Antony
      Mark-Antony
      Mark_Antony
      Mark+Antony

      I tried using ndata section but it is not helping, I tried using skipjoin in lexer parameter even that is not helping.
      Please give me a solution to achieve this functionality.

      Thanks & Regards,
      Vikas Krishna
        • 1. Re: Phrase Serach with special charaters in between
          Roger Ford-Oracle
          I'm not sure whether those are the queries, or the things that you want to find - but either way most of them should work by default.

          Some of the characters have special meaning in queries - eg "-" is the "minus" operator, so if that's in your search you may get different results.

          It would be best if you could show a full testcase of what you mean.
          • 2. Re: Phrase Serach with special charaters in between
            876250
            Please find what I am trying to achieve below.
            create table a_phrase_test(phrase clob);
            INSERT ALL
            INTO A_PHRASE_TEST VALUES ('Mark Antony')
            INTO A_PHRASE_TEST VALUES ('MARK ANTONY')
            INTO A_PHRASE_TEST VALUES ('mark antony')
            into a_phrase_test values ('Mark')
            into a_phrase_test values ('Antony')
            INTO A_PHRASE_TEST VALUES ('Cleopatra')
            select * from dual;
            commit;
            CREATE INDEX ix_phrase_test ON a_phrase_test(phrase) INDEXTYPE IS CTXSYS.CONTEXT;
            SQL> select * from a_phrase_test where contains (phrase,'Mark Antony') > 0;

            PHRASE
            --------------------------------------------------------------------------------
            Mark Antony
            MARK ANTONY
            mark antony
            SQL> select * from a_phrase_test where contains (phrase,'Mark+Antony') > 0
            2 /

            PHRASE
            --------------------------------------------------------------------------------
            Mark Antony
            MARK ANTONY
            mark antony
            SQL> select * from a_phrase_test where contains (phrase,'Mark/Antony') > 0;
            PHRASE
            --------------------------------------------------------------------------------
            Mark Antony
            MARK ANTONY
            mark antony
            Special characters + and / works like blank itself while doing phrase search , I am trying to achieve the same with other special characters also like _ ,\,-,|
            Please tell me how I can achieve the same. Also while using | symbol it gives results of both the terms, where as we are looking for occurances of both words together.

            Thanks & Regards,
            Vikas Krishna
            • 3. Re: Phrase Serach with special charaters in between
              Herald ten Dam
              Hi,

              the most simple way I solve this is to use translate on the search items:
              select * from a_phrase_test where contains (phrase,translate('Mark/Antony','_|/+','    ')) > 0;
              In the translate I put all the characters I don't want in the search. This also means you will not be able to search for those characters if they are to be found.
              If you want a more sophisticated way of determining which characters should stay in the search terms, then you need to define your own function in stead of translate, from which you will return the correct search items.

              Herald ten Dam
              http://htendam.wordpress.com
              • 4. Re: Phrase Serach with special charaters in between
                876250
                Thanks Herald.

                That will work for us. Thanks once again for the solution.

                Thanks & Regards,
                Vikas Krishna
                • 5. Re: Phrase Serach with special charaters in between
                  Barbara Boehmer
                  You can treat the special characters as whitespace, using a lexer as shown below.
                  SCOTT@orcl_11gR2> create table test_tab
                    2    (test_col  varchar2 (30))
                    3  /
                  
                  Table created.
                  
                  SCOTT@orcl_11gR2> insert all
                    2  into test_tab values ('Mark Antony')
                    3  into test_tab values ('Mark/Antony')
                    4  into test_tab values ('Mark\Antony')
                    5  into test_tab values ('Mark|Antony')
                    6  into test_tab values ('Mark-Antony')
                    7  into test_tab values ('Mark_Antony')
                    8  into test_tab values ('Mark+Antony')
                    9  into test_tab values ('Cleopatra')
                   10  select * from dual
                   11  /
                  
                  8 rows created.
                  
                  SCOTT@orcl_11gR2> begin
                    2    ctx_ddl.create_preference ('test_lex', 'basic_lexer');
                    3    ctx_ddl.set_attribute ('test_lex', 'whitespace', '/\|-_+');
                    4  end;
                    5  /
                  
                  PL/SQL procedure successfully completed.
                  
                  SCOTT@orcl_11gR2> create index test_idx
                    2  on test_tab (test_col)
                    3  indextype is ctxsys.context
                    4  parameters ('lexer test_lex')
                    5  /
                  
                  Index created.
                  
                  SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
                    2  /
                  
                  TOKEN_TEXT
                  ----------------------------------------------------------------
                  ANTONY
                  CLEOPATRA
                  MARK
                  
                  3 rows selected.
                  
                  SCOTT@orcl_11gR2> select * from test_tab
                    2  where  contains (test_col, 'Mark Antony') > 0
                    3  /
                  
                  TEST_COL
                  ------------------------------
                  Mark Antony
                  Mark/Antony
                  Mark\Antony
                  Mark|Antony
                  Mark-Antony
                  Mark_Antony
                  Mark+Antony
                  
                  7 rows selected.
                  
                  SCOTT@orcl_11gR2> select * from test_tab
                    2  where  contains (test_col, 'Mark+Antony') > 0
                    3  /
                  
                  TEST_COL
                  ------------------------------
                  Mark Antony
                  Mark/Antony
                  Mark\Antony
                  Mark|Antony
                  Mark-Antony
                  Mark_Antony
                  Mark+Antony
                  
                  7 rows selected.
                  
                  SCOTT@orcl_11gR2> select * from test_tab
                    2  where  contains (test_col, 'Mark/Antony') > 0
                    3  /
                  
                  TEST_COL
                  ------------------------------
                  Mark Antony
                  Mark/Antony
                  Mark\Antony
                  Mark|Antony
                  Mark-Antony
                  Mark_Antony
                  Mark+Antony
                  
                  7 rows selected.
                  • 6. Re: Phrase Serach with special charaters in between
                    876250
                    Thanks a lot Barbara. You are so awesome.


                    Thanks & Regards,
                    Vikas Krishna