Re: find email address in clob

From: Hexathioorthooxalate <ruler_at_remov_eme@clara.co.uk>
Date: Fri, 5 Mar 2004 15:08:25 -0000
Message-ID: <1078499306.77406.0@iris.uk.clara.net>

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_at_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_at_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_at_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_at_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_at_spamex.com> wrote in message news:bQ%1c.9073$jw2.615698_at_news20.bellglobal.com...
>
> "Cookie Monster" <cookie.monster_at_somewhere.com> a écrit dans le message de
> news:c29fj6$1pqikj$1_at_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.
>
>
Received on Fri Mar 05 2004 - 09:08:25 CST