From: kennedy-downwithspammersfamily@attbi.net   
      
   "Michael"  wrote in message   
   news:e7b6653e.0405161236.7d7dfc87@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)