Bib, I'm not so convinced. I initially believed the issue was that a
BASIC_LEXER printjoin character needed defining, but then I observed it
without the addition of further printjoins (see trace below).
Cookie Monster, /*after*/ you have done the table INSERT, and /*before*/ you
do the SELECT, have you rebuilt your indexes ALTER INDEX ... REBUILD?
Remember when you do the INSERT, the underlying index MAY NOT be immediately
updated! This is a common gotcha.
Regards
Hexathioorthooxalate
SQL>
SQL>
SQL> CREATE TABLE tblTest(ContactDetails VARCHAR2(100));
Table created.
SQL>
SQL> INSERT INTO tblTest(ContactDetails)
2 VALUES ('jbloggs@anon.com');
1 row created.
SQL> INSERT INTO tblTest(ContactDetails)
2 VALUES ('joe bloggs');
1 row created.
SQL>
SQL> CREATE INDEX idxTest
2 ON tblTest(ContactDetails)
3 INDEXTYPE IS CTXSYS.CONTEXT;
Index created.
SQL>
SQL>
SQL> SELECT ContactDetails
2 FROM tblTest
3 WHERE CONTAINS(ContactDetails,'@anon.com')>0;
CONTACTDETAILS
---------------------------------------------------------
jbloggs@anon.com
SQL>
SQL>
SQL> -- BUT, SEE MY NOTE ABOUT AN INDEX REBUILD ABOVE
SQL> ------------------------------------------------
SQL> ------------------------------------------------
SQL> DROP TABLE tblTest;
Table dropped.
SQL>
SQL> CREATE TABLE tblTest(ContactDetails VARCHAR2(100));
Table created.
SQL>
SQL> CREATE INDEX idxTest
2 ON tblTest(ContactDetails)
3 INDEXTYPE IS CTXSYS.CONTEXT;
Index created.
SQL>
SQL> INSERT INTO tblTest(ContactDetails)
2 VALUES ('jbloggs@anon.com');
1 row created.
SQL> INSERT INTO tblTest(ContactDetails)
2 VALUES ('joe bloggs');
1 row created.
SQL> -- NOTE NO ROWS RETURNED
SQL> ------------------------------------------------
SQL> SELECT ContactDetails
2 FROM tblTest
3 WHERE CONTAINS(ContactDetails,'@anon.com')>0;
no rows selected
SQL>
SQL> --THIS IS THE DIFFERENCE
SQL> -------------------------
SQL> ALTER INDEX idxTest REBUILD;
Index altered.
SQL> -------------------------
SQL> -------------------------
SQL>
SQL> SELECT ContactDetails
2 FROM tblTest
3 WHERE CONTAINS(ContactDetails,'@anon.com')>0;
CONTACTDETAILS
---------------------------------------------------------
jbloggs@anon.com
SQL>
SQL>
SQL>
SQL> SELECT *
2 FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL>
"Bib Endum" <gd-newsgroups@spamex.com> wrote in message
news:bQ%1c.9073$jw2.615698@news20.bellglobal.com.. .
>
> "Cookie Monster" <cookie.monster@somewhere.com> a écrit dans le message de
> news:c29fj6$1pqikj$1@ID-82797.news.uni-berlin.de...
> > Hi,
> >
> > I am using Oracle 9i Text and I have the below query statement which
> returns
> > the wrong results:
> >
> > select * from mytable
> > where contains(a_text,'@msn.com') > 0
> >
> > The problem is this returns ALL documents with msn.com and not the
> documents
> > containing only '@msn.com' basically the @ is ignored. I just want to
find
> > the email addresses. Does anyone know how I can force a search for this
> > query and get oracle text to use the @ ?? I tried escaping the character
> and
> > everything but this no affect on the results.
> >
>
> The problem is that maybe the @ character is in the stoplist, as are most
> punctuation character and is not indexed per se.
>
>