1 Reply Latest reply: May 14, 2014 7:28 AM by J0nas RSS

    Case sensitivy in 11.2.0.1 and 11.2.0.4

    J0nas

      I didn't find an answer to this question so I'm posting it here:

       

      Is there a difference in handling search terms within oracle text regarding case sensitivity?

       

      I have two Oracle instances (11.2.0.1 and 11.2.0.4) containing the exact same data.

      On 11.2.0.1 i get some results with this query, where i don't in 11.2.0.4:

       

      SELECT mc.message_id FROM message_comments mc WHERE CONTAINS(mc.comment_text, '{test}', 1) > 0

       

      comment_text is actually 'Test'.

       

      I hope someone can give me an answer to this, thanks!

        • 1. Re: Case sensitivy in 11.2.0.1 and 11.2.0.4
          J0nas

          I found the following:

          By default, all text tokens are converted to uppercase and then indexed. This results in case-insensitive queries. For example, separate queries on each of the three words cat, CAT, and Cat all return the same documents.

           

          I discovered that in my 11.2.0.1 DB all tokens are uppercase where in the 11.2.0.4 DB they aren't. Why is that?

           

          -e-

          I checked what lexer I'm using:

           

          SQL>  SELECT par_value from ctx_parameters WHERE par_name = 'DEFAULT_LEXER';

           

          PAR_VALUE

          --------------------------------------------------------------------------------

          CTXSYS.DEFAULT_LEXER

           

          Setting mixed_case to no:

           

          EXEC CTX_DDL.SET_ATTRIBUTE ('CTXSYS.DEFAULT_LEXER', 'MIXED_CASE', 'NO')

           

          rebuilding index:

           

          ALTER INDEX I_MSG_COMMENTS_CMT_TXT REBUILD;

           

          Still the same, queries are case sensitive.

           

          -e-

           

          I had to drop and recreate the index, now it works!