Re: Full text indexes and hints

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1094496473.998991_at_yasure...
> > I need to query the contract_text column (a bunch of text) case
sensitively
> > and case insensitively using Oracle full-text via the CONTAINS function.
> > When you "build" a full text index it can be created either case
> > insensitively (this is the default) or case sensitively.
> > Therefore if I want to query the column in a case sensitive and
insensitive
> > manner I need two "full text indexes" (I think :-))
> > So what I want to do is:
> >
> > SELECT id FROM contracts
> > WHERE CONTAINS(contract_text, 'dog')>0 -- case insensitive
> > AND CONTAINS (contract_text, 'Dog')>0 -- case sensitive
>
> I think you need to seriously reconsider what you are asking.
>
> WHERE CONTAINS(contract_text, 'dog')>0 -- case insensitive
>
> will, by definition, get every occurence of the letter D followed
> by the letter O followed by the letter G no matter what case they are.
> So you case sensitive search is both redundant and pointless.
>

Cheers for the continued help:

To quote from the Oracle docs regarding "full text indexes":

"By default, each word is uppercased during indexing so that queries are case-independent. MIXED_CASE is a boolean, which, if set to YES, does not do this. This makes indexing and queries case-sensitive. By default, each word is uppercased during indexing so that queries are case-independent. MIXED_CASE is a boolean, which, if set to YES, does not do this. This makes indexing and queries case-sensitive."

http://www.oracle.com/technology/products/text/x/tech_overviews/imt_815.html Received on Tue Sep 07 2004 - 02:44:52 CDT