Home » Server Options » Text & interMedia » Oracle 11.2 text search using contains operator for % wildcard (Oracle 11.2)
Oracle 11.2 text search using contains operator for % wildcard [message #593641] Mon, 19 August 2013 09:34 Go to next message
Shrridevi
Messages: 1
Registered: August 2013
Junior Member
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 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.
Re: Oracle 11.2 text search using contains operator for % wildcard [message #593661 is a reply to message #593641] Mon, 19 August 2013 17:06 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
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.

Previous Topic: Oracle Text Index Sync Problem on Update
Next Topic: progressive relaxation techniques
Goto Forum:
  


Current Time: Thu Mar 28 15:44:35 CDT 2024