"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
Received on Sun May 16 2004 - 15:49:30 CDT
