Re: Oracle Text with Numbers

From: Michael <mschott14_at_hotmail.com>
Date: 16 May 2004 22:04:14 -0700
Message-ID: <e7b6653e.0405162104.73cd4549_at_posting.google.com>


"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<utQpc.61946$z06.8535347_at_attbi_s01>...
> "Michael" <mschott14_at_hotmail.com> wrote in message
> news:e7b6653e.0405161236.7d7dfc87_at_posting.google.com...
> > Hello,
> >
> > I need to search in a number column for particular "subnumbers". For
> > example I have a column with 3453454 in it an I like to searh for the
> > number "53" in it. I know I could use
> >
> > select * from table where number_column like '%53%'
> >
> > but since the table is rather big I'd like to use Oracle Text for it
> > and query like
> >
> > select * from table where contains(number_column, 53) > 0
> >
> > but above query would return NULL after converting the number column
> > to a varchar2 column! Only full numbers are indexed and therefore only
> > a search on the full number 3453454 would yield a result. What are my
> > options to make above query with "contains" clause work?
> >
> > Thanks in advance
> Oracle text won't help since Oracle Text is looking for words not
> substrings. I can't think of a Mathmatical transformation that would then
> use an index on the column. Is it always 53 or could it be any substring?
> If it was always 53 then you could have a function based index. I think you
> are going to have to do:
> select * from table where to_char(number_column) like '%53%' and have to do
> a full table scan.
> Jim

Thanks for your prompt reply.

it could be any substring, thats why I need sth.like above query

select * from table where contains(number_column, '234') > 0

there must be a (better) solution to avoid the full table scan. Any other opinions,suggestions?

Thanks, Michael Received on Mon May 17 2004 - 07:04:14 CEST

Original text of this message