5 Replies Latest reply: Aug 20, 2013 1:20 PM by Barbara Boehmer RSS

    Oracle text search with % doest give result in 11.2 version

    175f84bd-da42-4818-8273-b163a780867d

      I have a column for which oracle text index is created. Based on the text search given by the user I need to filter the data. If the user enters % i need to show all the results. When i use the below query in oracle 11.2 version I get zero results but in 11.1 version i get the proper results.

       

      select * from table where contains(colum_name,'%')>0;

       

      Do we need to make wildcard search in a different way in oracle 11.2 ? Because this query works fine in 11.1 version of Oracle.

        • 1. Re: Oracle text search with % doest give result in 11.2 version
          Barbara Boehmer

          You can union all a separate select as demonstrated below.

           

           

          SCOTT@orcl12c_11gR2> select banner from v$version

            2  /

           

          BANNER

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

          Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

          PL/SQL Release 12.1.0.1.0 - Production

          CORE    12.1.0.1.0    Production

          TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

          NLSRTL Version 12.1.0.1.0 - Production

           

          5 rows selected.

           

          SCOTT@orcl12c_11gR2> create table table_name

            2    (column_name  varchar2(60))

            3  /

           

          Table created.

           

          SCOTT@orcl12c_11gR2> insert into table_name (column_name) values ('test data')

            2  /

           

          1 row created.

           

          SCOTT@orcl12c_11gR2> insert into table_name (column_name) values ('more data')

            2  /

           

          1 row created.

           

          SCOTT@orcl12c_11gR2> create index oracle_text_index on table_name (column_name)

            2  indextype is ctxsys.context

            3  /

           

          Index created.

           

          SCOTT@orcl12c_11gR2> variable search_string varchar2(100)

          SCOTT@orcl12c_11gR2> exec :search_string := '%'

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c_11gR2> select * from table_name

            2  where  contains (column_name, :search_string) > 0

            3  /

           

          no rows selected

           

          SCOTT@orcl12c_11gR2> select * from table_name

            2  where  :search_string != '%'

            3  and    contains (column_name, :search_string) > 0

            4  union all

            5  select * from table_name

            6  where  :search_string = '%'

            7  /

           

          COLUMN_NAME

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

          test data

          more data

           

          2 rows selected.

           

          SCOTT@orcl12c_11gR2> exec :search_string := 'test'

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c_11gR2> select * from table_name

            2  where  :search_string != '%'

            3  and    contains (column_name, :search_string) > 0

            4  union all

            5  select * from table_name

            6  where  :search_string = '%'

            7  /

           

          COLUMN_NAME

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

          test data

           

          1 row selected.

          • 2. Re: Oracle text search with % doest give result in 11.2 version
            kevinUCB

            A trick I've used when the circumstances allowed it is to use an SDATA section (in my case, it was a yes/no flag) that allows me to test for NULL as the 'additional' criteria.

            So my query looks like "...contains(column_name, '% and yes_no_data is not null')>0"

             

            This has the additional benefit of working with the XML Resultset interface, where structured criteria can't be used.

            • 3. Re: Oracle text search with % doest give result in 11.2 version
              175f84bd-da42-4818-8273-b163a780867d

              Thanks for the reply. I wanted to know why the % search with contains not giving results in 11.2 version. Still it works fine in 11.1. I even checked release notes if something is mentioned about wildcard search could not find any details.

              • 4. Re: Oracle text search with % doest give result in 11.2 version
                kevinUCB

                All I can really say is that the behavior did change.

                If I were Oracle, I would answer you by claiming that we're making it more google-like. A google query for '*' returns no rows.

                • 5. Re: Oracle text search with % doest give result in 11.2 version
                  Barbara Boehmer

                  I agree with what Kevin said that returning all rows when searching for just '%' was probably considered a mistake and rectified.  However, I would like to point out that it helps to provide your complete version including all four decimal places, as this can matter.  Please see the example below that shows that 11.2.0.1.0 on Windows 7 Professional 64-bit does (incorrectly) return all rows when searching for '%'.

                   

                  SCOTT@orcl_11gR2> select banner from v$version

                    2  /

                  BANNER

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

                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                  PL/SQL Release 11.2.0.1.0 - Production

                  CORE    11.2.0.1.0    Production

                  TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

                  NLSRTL Version 11.2.0.1.0 - Production

                  5 rows selected.

                  SCOTT@orcl_11gR2> create table table_name

                    2    (column_name  varchar2(60))

                    3  /

                  Table created.

                  SCOTT@orcl_11gR2> insert into table_name (column_name) values ('test data')

                    2  /

                  1 row created.

                  SCOTT@orcl_11gR2> insert into table_name (column_name) values ('more data')

                    2  /

                  1 row created.

                  SCOTT@orcl_11gR2> create index oracle_text_index on table_name (column_name)

                    2  indextype is ctxsys.context

                    3  /

                  Index created.

                  SCOTT@orcl_11gR2> variable search_string varchar2(100)

                  SCOTT@orcl_11gR2> exec :search_string := '%'

                  PL/SQL procedure successfully completed.

                  SCOTT@orcl_11gR2> select * from table_name

                    2  where  contains (column_name, :search_string) > 0

                    3  /

                  COLUMN_NAME

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

                  test data

                  more data

                  2 rows selected.

                  SCOTT@orcl_11gR2> select * from table_name

                    2  where  contains (column_name, '%') > 0

                    3  /

                  COLUMN_NAME

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

                  test data

                  more data

                  2 rows selected.