Re: Excluding values with numbers

From: Pankaj <harpreet.noni_at_gmail.com>
Date: Sun, 3 Jan 2010 11:43:43 -0800 (PST)
Message-ID: <57cf5631-ae5a-4236-8cd8-c271a58eb974_at_m16g2000yqc.googlegroups.com>

On Jan 1, 4:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jan 1, 12:13 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
>
>
> > Here is a solution that uses a translate function/.  My resutl vary
> > because I could not remember the actual starting letters specified by
> > the OP as I do not have access to Oracle and the forum at the same
> > time.  I made my solution case sensitive and used "b,g, and h".  I
> > added two rows to ensure at least one row that started with one of the
> > exclude letters when followed by digits whould appear in the output.
>
> > 1 > select * from t10
> >   2  where homework not in (
> >   3    select homework
> >   4    from t10
> >   5    where ( substr(homework,1,1) in ('b','g','h')
> >   6    and instr(translate(homework,'012345678','999999999'),'9') >
> > 0 ))
> >   7  /
>
> > HOMEWORK
> > --------------------
> > a12345
> > A123423
> > G452323
> > r34323
> > n232323
> > NB151517
> > C0151517
> > f9151517
> > HE4423
> > hxxxxxxx          -- added
> > gabcdefg          -- added
>
> > 11 rows selected.
>
> > The above assumes that all the data is of the form Letter || digits
> > and that no data with mixed letters and digits where the presence of
> > letters should cause the data to not be excluded.  The following would
> > handle data with those rules using something like h123x as a test
> > case.
>
> >   5    where ( substr(homework,1,1) in ('b','g','h')
> >   6    and       replace(translate(substr(homework,2,length
> > (homework)),
> >   7            '012345678','999999999'),'9','') is null
>
> > Using an upper or lower rtrim depending on case sensitivity desired as
> > Maxum demostrated does seem a lot slicker of a solution.
>
> > HTH  and hoping I did not make some stupid typo -- Mark D Powell
>
> Nice example with the TRANSLATE function.
>
> If the OP were running Oracle 10g R1 or later the following would also
> work:
> (REGEXP_INSTR)
> SELECT
>   HOMEWORK
> FROM
>   T10
> WHERE
>   REGEXP_INSTR(UPPER(HOMEWORK),'[HBN][0123456789]')<>1;
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> (Shortened version of the above)
> SELECT
>   HOMEWORK
> FROM
>   T10
> WHERE
>   REGEXP_INSTR(UPPER(HOMEWORK),'[HBN][0-9]')<>1
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> (REGEXP_REPLACE)
> SELECT
>   HOMEWORK
> FROM
>   T10
> WHERE
>   REGEXP_REPLACE(SUBSTR(UPPER(HOMEWORK),1,2),'[HBN][0123456789]',NULL)
> IS NOT NULL;
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> There must be a couple more ways to solve this SQL problem.
>
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"http://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thanks Everyone. I have got more than what I expected. I would like to thanks everyone again.

Charles: I was able to work it out using TRANSLATE but have alredy replaced with new solution given my others.

Thanks again. Received on Sun Jan 03 2010 - 13:43:43 CST