OraFAQ Forum: Text & interMedia » Oracle 11.2 text search using contains operator for % wildcard

Home » Server Options » Text & interMedia » Oracle 11.2 text search using contains operator for % wildcard (Oracle 11.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle 11.2 text search using contains operator for % wildcard [message #593641]

Mon, 19 August 2013 09:34

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.

Report message to a moderator

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

Barbara Boehmer
Messages: 8922
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 09 12:16:50 CDT 2022