SEO

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.

Thanks,
Cookie.

A Wild guess here .. No way for me to test it..

select * from mytable
where contains(a_text,'\@msn.com') > 0 Escape '\'

--
Cheers,
Ganesh Raja


"Cookie Monster" <cookie.monster@somewhere.com> wrote in message
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.
>
> Thanks,
> Cookie.
>
>

"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.

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.
>
>

Cookie Monster wrote:
> 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.
>
> Thanks,
> Cookie.
>

try

chr(64) || 'msn.com'

as an alternative.