Home » Server Options » Text & interMedia » issue with Contains() in Oracle 9i (merged 3) (Oracle 9i)
issue with Contains() in Oracle 9i (merged 3) [message #557731] Fri, 15 June 2012 04:55 Go to next message
mandark
Messages: 23
Registered: October 2009
Junior Member
Hi,

I have following issue with contains() function:

select * from producttitle pt
where pt.Title like '%GIRL WHO%'

Above query returns 5 results.


But when I use contains() instead of like, no rows are returned.

select * from producttitle pt
where contains(pt.Title,'%GIRL WHO%') > 0

Thanks,
Mandar
Re: issue with Contains() in Oracle 9i (merged 3) [message #557761 is a reply to message #557731] Fri, 15 June 2012 09:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
"Who" is a default stopword. If you do not specify a stoplist in your parameters during index creation, then it uses the default_stoplist. If you do not use a stoplist without the stopword that you are searching for, then it affects the whole query. I have used ctxsys.empty_stoplist in the example below. You, of course, need to make sure that either you created your index after inserts or you synchronized after inserts. Also, the syntax of contains is not the same as the syntax of like. You do not need to use % wildcards to find whole words within a string. You only need to use %wildcards to find portions of words. Please see the demonstration below.

SCOTT@orcl_11gR2> CREATE TABLE producttitle (title  VARCHAR2(30))
  2  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO producttitle (title) VALUES ('GIRL WHO')
  3  INTO producttitle (title) VALUES ('THAT GIRL WHO')
  4  INTO producttitle (title) VALUES ('AGIRL WHOM')
  5  INTO producttitle (title) VALUES ('THE GIRL WHO DID IT')
  6  INTO producttitle (title) VALUES ('ONE GIRL WHO DID NOT')
  7  INTO producttitle (title) VALUES ('OTHER DATA')
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_11gR2> SELECT * FROM producttitle pt
  2  WHERE  pt.title LIKE '%GIRL WHO%'
  3  /

TITLE
------------------------------
GIRL WHO
THAT GIRL WHO
AGIRL WHOM
THE GIRL WHO DID IT
ONE GIRL WHO DID NOT

5 rows selected.

SCOTT@orcl_11gR2> CREATE INDEX title_idx
  2  ON producttitle (title)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST')
  5  /

Index created.

SCOTT@orcl_11gR2> SELECT * FROM producttitle pt
  2  WHERE  CONTAINS (pt.title, 'GIRL WHO') > 0
  3  /

TITLE
------------------------------
GIRL WHO
THAT GIRL WHO
THE GIRL WHO DID IT
ONE GIRL WHO DID NOT

4 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM producttitle pt
  2  WHERE  CONTAINS (pt.title, '%GIRL WHO%') > 0
  3  /

TITLE
------------------------------
GIRL WHO
THAT GIRL WHO
AGIRL WHOM
THE GIRL WHO DID IT
ONE GIRL WHO DID NOT

5 rows selected.

Re: issue with Contains() in Oracle 9i (merged 3) [message #557794 is a reply to message #557761] Fri, 15 June 2012 15:27 Go to previous messageGo to next message
Littlefoot
Messages: 19678
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it the girl who played with fire?? ./fa/1599/0/
Re: issue with Contains() in Oracle 9i (merged 3) [message #557817 is a reply to message #557794] Fri, 15 June 2012 22:48 Go to previous message
mandark
Messages: 23
Registered: October 2009
Junior Member
Thank you barbara for solution.

Your replies are always bang on!!

Thanks Smile
Previous Topic: ORA-00949: illegal reference to remote database with catsearch
Next Topic: (INSERT/UPDATE/MERGE/DELETE) Slow performance on table after creating CTXSYS.CONTEXT text index on o
Goto Forum:
  


Current Time: Mon Oct 20 13:04:35 CDT 2014

Total time taken to generate the page: 0.09857 seconds