Re: Excluding values with numbers
From: joel garry <joel-garry_at_home.com>
Date: Tue, 29 Dec 2009 09:30:07 -0800 (PST)
Message-ID: <322df4e7-13a2-4efb-8ef2-a8892b4d6d91_at_a21g2000yqc.googlegroups.com>
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).
Date: Tue, 29 Dec 2009 09:30:07 -0800 (PST)
Message-ID: <322df4e7-13a2-4efb-8ef2-a8892b4d6d91_at_a21g2000yqc.googlegroups.com>
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, like http://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-Hacker-Target-In-2010?art_pos=1Received on Tue Dec 29 2009 - 11:30:07 CST