Re: Experts plese help me..

Hi.

 Assuming that there is no space/blanks between the string 'CP#' and  following number try :

 SELECT * FROM your_table
 WHERE your_field LIKE 'CP#%' AND
  LPAD(TRANSLATE(SUBSTR(your_field,4,(INSTR(your_field,' ') -4)),     '0123456789','9999999999'),20,'9') =

  '99999999999999999999' );

It will take care of all occurrences where the number of digit is <= 20.

HTH. Michael.

In article <3833A6C8.4D50C7F9_at_ti.com>,
  norazman_at_ti.com 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 19 1999 - 09:56:00 CST