Re: Excluding values with numbers
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Wed, 30 Dec 2009 04:02:38 -0800 (PST)
Message-ID: <fd0a6735-3a8b-4289-8112-a9af80ec3efd_at_m3g2000yqf.googlegroups.com>
On Dec 29, 6:30 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 29, 8:54 am, Pankaj <harpreet.n..._at_gmail.com> wrote:
>
>
>
> > Greetings,
>
> > I am currently using Oracle9i Enterprise Edition Release 9.2.0.4.0. I
> > have a table with following data
>
> > Table 1 (Sample data)
> > a12345
> > A123423
> > g13452
> > G452323
> > h34423
> > r34323
> > b23232
> > n232323
>
> > I am currently using this as a subquery in one of the query. As per a
> > new request I have to now exclude all values which start with h, b or
> > n followed by numeric values.
>
> > So end result the subquery should give me is
>
> > Table 1 (Sample data)
> > a12345
> > A123423
> > g13452
> > G452323
> > r34323
>
> > I am little stumped on this for now. Could not get it right in my
> > query. Can anyone please advise here. Let me know if any more
> > information is needed from my side.
>
> > Note: The starting character in all values can sometimes in "lower
> > case" or sometimes in "upper case".
>
> > TIA
>
> Some people prefer that you post create table, insert data and what
> sql statements you've tried, so they can quickly ramp up a test
> environment. Is this school or work? (If school, people are usually
> willing to give you hints on how to figure it out, not do it for you).
>
> You could substr, uppercase and notinlist for the first character in
> your where statement, and there are several ways to check the rest for
> numeric, likehttp://www.adp-gmbh.ch/ora/plsql/helpers/is_numeric.html
>
> jg
> --
> _at_home.com is bogus.
> Two weeks...http://it.slashdot.org/story/09/12/29/1435259/Adobe-Flash-To-Be-Top-H...
Date: Wed, 30 Dec 2009 04:02:38 -0800 (PST)
Message-ID: <fd0a6735-3a8b-4289-8112-a9af80ec3efd_at_m3g2000yqf.googlegroups.com>
On Dec 29, 6:30 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 29, 8:54 am, Pankaj <harpreet.n..._at_gmail.com> wrote:
>
>
>
> > Greetings,
>
> > I am currently using Oracle9i Enterprise Edition Release 9.2.0.4.0. I
> > have a table with following data
>
> > Table 1 (Sample data)
> > a12345
> > A123423
> > g13452
> > G452323
> > h34423
> > r34323
> > b23232
> > n232323
>
> > I am currently using this as a subquery in one of the query. As per a
> > new request I have to now exclude all values which start with h, b or
> > n followed by numeric values.
>
> > So end result the subquery should give me is
>
> > Table 1 (Sample data)
> > a12345
> > A123423
> > g13452
> > G452323
> > r34323
>
> > I am little stumped on this for now. Could not get it right in my
> > query. Can anyone please advise here. Let me know if any more
> > information is needed from my side.
>
> > Note: The starting character in all values can sometimes in "lower
> > case" or sometimes in "upper case".
>
> > TIA
>
> Some people prefer that you post create table, insert data and what
> sql statements you've tried, so they can quickly ramp up a test
> environment. Is this school or work? (If school, people are usually
> willing to give you hints on how to figure it out, not do it for you).
>
> You could substr, uppercase and notinlist for the first character in
> your where statement, and there are several ways to check the rest for
> numeric, likehttp://www.adp-gmbh.ch/ora/plsql/helpers/is_numeric.html
>
> jg
> --
> _at_home.com is bogus.
> Two weeks...http://it.slashdot.org/story/09/12/29/1435259/Adobe-Flash-To-Be-Top-H...
I've used TRANSLATE() a lot in tasks similar to the one described by op.
You may want to take a look at it.
HTH. Cheers.
Carlos. Received on Wed Dec 30 2009 - 06:02:38 CST