| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Experts plese help me..
Paul,
select * from the_table where the_column like '%CP%'; minus the first % is what I first considered but as you pointed out he needs a NOTFOUND flag which the above query does not return without then performing another action to determine which rows were not returned in the first query. In my response I did say that within the cursor he would have to check for the existence of CP# which you are doing with your query but based on whether or not CP# is found he could either return the row_ID or whatever he needs, or, a NOTFOUND flag as he sees fit. Agreed, if he is dealling with 50,000,000 rows a cursor may prove difficult but then again I got the impression that it was a lot but only by the standards that it was too much to do by hand and still be time efficient. Just by the fact he even thought about doing it by hand implies, to me anyway, that the number is not even close to 50,000,000 but probably relativly small in terms of a database (i.e. < 100,000) which should not be much of a problem for a cursor.
What do you think?
Darren
paul cluiss wrote:
>
> Darren,
>
> I think Norazman is saying that his table contains too many records for a
> linear search to be practical, and wants instead a presumably faster SQL
> search. I am guessing that he needs to perform this search interactively, so
> searching all 50,000,000 (or however many) records might take too long. I
> further speculate that if the string is not found in any record, then he needs
> to return some sort of NOTFOUND flag, whereupon some alternative action is
> taken.
>
> If these assumptions are correct, I propose this SQL statement:
>
> select * from the_table where the_column like '%CP%';
>
> Well, Norazman, what do you think?
>
> Paul Cluiss
> Dallas, Texas
>
> Darren Brock wrote:
>
> > One way that you can do this is with a cursor. Loop through all the
> > records and for each one, check to see if it contains CP# and if it
> > does, perform a select and substring out the part that you want stuffing
> > appropriate info into a temp table.
> >
> > Darren
> >
> > Norazman wrote:
> > >
> > > Hi all,
> > > I have a description field which user use to enter some description.
> > > However due to bad design (from the earlier designer), I need a way of
> > > how I can figure this out.
> > >
> > > This field can contains any words but usually it was also use to key in
> > > some Capital number.
> > > It might be something like this:
> > >
> > > CP#4599 ACERPOWER 4100, 64MB SDRAM DIMM(upgradable to 384MB)
> > >
> > > However, I'm only interested on the Capital number not the nonsense
> > > word.
> > > Normally the format (the way usr keyed) in is 'CP% 999999'.
> > >
> > > The problem is, the numbers of record is so huge that I don't want to go
> > > through it 1 by 1.
> > > How can I do a SQL to return only whatever from this field that sounds
> > > like 'CP% 999999' and if nothing match just
> > > return N/A or whatever to indicate the search string is not exist.
> > >
> > > So far I can say around 65-70% of the field have this string.
> > >
> > > Thanks for your help.
> > >
> > > Azman
> >
> > --
> > ***********************************************
> > Darren Brock Oracle Developer/DBA
> > Governet
> > 208-522-1225
> > brock_at_governet.net
> > ***********************************************
--
 |
 |