Path: g2news1.google.com!postnews.google.com!k19g2000yqc.googlegroups.com!not-for-mail
From: Mark D Powell <Mark.Powe...@hp.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Excluding values with numbers
Date: Fri, 1 Jan 2010 09:13:03 -0800 (PST)
Organization: http://groups.google.com
Lines: 324
Message-ID: <948e0e28-9efe-4902-9ceb-de2c1ee8b93b@k19g2000yqc.googlegroups.com>
References: <5b741819-135e-4812-a75e-e9342954f42c@h10g2000vbm.googlegroups.com> 
	<92f72abb-3a28-4f40-822c-431b928acf2b@o28g2000yqh.googlegroups.com> 
	<895685f9-0295-439e-93f7-9b45e264f570@n38g2000yqf.googlegroups.com> 
	<8c45e8d0-405f-4fad-9d18-6a0ccf5485e8@n38g2000yqf.googlegroups.com> 
	<f76fe411-a96a-4cce-a887-f24cea271ea7@m3g2000yqf.googlegroups.com> 
	<4B3D09ED.3070404@gmail.com> <2a3f7009-12a8-45bb-a42f-6f87610f6a19@26g2000yqo.googlegroups.com> 
	<64387ce3-e135-42ff-b135-00f83fe7683f@u41g2000yqe.googlegroups.com> 
	<94349667-27f9-440f-9c51-dfe2214c079b@c3g2000yqd.googlegroups.com>
NNTP-Posting-Host: 69.245.100.1
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1262365983 24146 127.0.0.1 (1 Jan 2010 17:13:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 1 Jan 2010 17:13:03 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: k19g2000yqc.googlegroups.com; posting-host=69.245.100.1; 
	posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; 
	Trident/4.0; .NET CLR 1.0.3705; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET 
	CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.0.4506.2152; .NET CLR 
	3.5.30729; OfficeLiveConnector.1.3; OfficeLivePatch.0.0),gzip(gfe),gzip(gfe)

On Dec 31 2009, 10:34=A0pm, Charles Hooper <hooperc2...@yahoo.com>
wrote:
> On Dec 31, 6:19=A0pm, Pankaj <harpreet.n...@gmail.com> wrote:
>
> > Maxim: Thanks for explanation and approach from your side as well. One
> > clarification, I currently also have data where values are all
> > alphabets (like hhhhh, bbbb, nnnnn, gggg, kkkk). My requirement says
> > that I only have to exclude those values that start with h, n alphabet
> > and followed by a numeric value. So i belive we do need to include a
> > scenario to make sure next character is numeric.
>
> > TIA
>
> Maxim's solution is quite impressive. =A0Here is an explanation of his
> solution:
> SELECT
> =A0 *
> FROM
> =A0 T10;
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> h34423
> r34323
> b23232
> n232323
> NB151517
> C0151517
> f9151517
> HE4423
>
> The demo table has 12 rows.
>
> The first part of his solution does this:
> SELECT
> =A0 HOMEWORK,
> =A0 RTRIM(HOMEWORK,'0123456789') TEST
> FROM
> =A0 T10;
>
> HOMEWORK =A0 TEST
> ---------- ----
> a12345 =A0 =A0 a
> A123423 =A0 =A0A
> g13452 =A0 =A0 g
> G452323 =A0 =A0G
> h34423 =A0 =A0 h
> r34323 =A0 =A0 r
> b23232 =A0 =A0 b
> n232323 =A0 =A0n
> NB151517 =A0 NB
> C0151517 =A0 C
> f9151517 =A0 f
> HE4423 =A0 =A0 HE
>
> Notice in the above that the TEST column shows that the RTRIM function
> eliminated everything to the right of the first digit, including that
> first digit. =A0Then, his solution simply determines if what is left (in
> the TEST column) is one of h, b, or n, and if it is, the row is
> eliminated.
>
> The output of Maxim's solution:
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> WHERE
> =A0 NOT LOWER(RTRIM(HOMEWORK,'0123456789')) IN ('h','b','n');
>
> HOMEWORK
> ---------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> So, Maxim's solution does what you want.
>
> Here are a couple more solutions:
> The silly way with a MINUS operation:
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> MINUS
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> WHERE
> =A0 UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> =A0 AND SUBSTR(HOMEWORK,2,1) IN
> ('1','2','3','4','5','6','7','8','9','0');
>
> HOMEWORK
> --------
> A123423
> C0151517
> G452323
> HE4423
> NB151517
> a12345
> f9151517
> g13452
> r34323
>
> The neat solution with MINUS:
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> MINUS
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> WHERE
> =A0 UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> =A0 AND SUBSTR(HOMEWORK,2,1) IN (
> =A0 =A0 SELECT
> =A0 =A0 =A0 TO_CHAR(ROWNUM-1)
> =A0 =A0 FROM
> =A0 =A0 =A0 DUAL
> =A0 =A0 CONNECT BY
> =A0 =A0 =A0 LEVEL<=3D10);
>
> HOMEWORK
> --------
> A123423
> C0151517
> G452323
> HE4423
> NB151517
> a12345
> f9151517
> g13452
> r34323
>
> The NOT method:
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> WHERE
> =A0 NOT(UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> =A0 =A0 AND SUBSTR(HOMEWORK,2,1) IN
> ('1','2','3','4','5','6','7','8','9','0'));
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> The neat solution with NOT:
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> WHERE
> =A0 NOT(UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> =A0 =A0 AND SUBSTR(HOMEWORK,2,1) IN (
> =A0 =A0 =A0 SELECT
> =A0 =A0 =A0 =A0 TO_CHAR(ROWNUM-1)
> =A0 =A0 =A0 FROM
> =A0 =A0 =A0 =A0 DUAL
> =A0 =A0 =A0 CONNECT BY
> =A0 =A0 =A0 =A0 LEVEL<=3D10));
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> The left outer join method:
> SELECT
> =A0 T10.HOMEWORK
> FROM
> =A0 T10,
> =A0 (SELECT
> =A0 =A0 HOMEWORK
> =A0 FROM
> =A0 =A0 T10
> =A0 WHERE
> =A0 =A0 (UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N'))
> =A0 =A0 AND (SUBSTR(HOMEWORK,2,1) IN (
> =A0 =A0 =A0 SELECT
> =A0 =A0 =A0 =A0 TO_CHAR(ROWNUM-1)
> =A0 =A0 =A0 FROM
> =A0 =A0 =A0 =A0 DUAL
> =A0 =A0 =A0 CONNECT BY
> =A0 =A0 =A0 =A0 LEVEL<=3D10))) NT10
> WHERE
> =A0 T10.HOMEWORK=3DNT10.HOMEWORK(+)
> =A0 AND NT10.HOMEWORK IS NULL;
>
> HOMEWORK
> --------
> A123423
> C0151517
> r34323
> HE4423
> g13452
> f9151517
> a12345
> G452323
> NB151517
>
> The Cartesian join method:
> SELECT
> =A0 HOMEWORK
> FROM
> =A0 T10
> WHERE
> =A0 UPPER(SUBSTR(HOMEWORK,1,2)) NOT IN
> (SELECT
> =A0 L||N
> FROM
> =A0 (SELECT
> =A0 =A0 DECODE(ROWNUM,1,'H',2,'B',3,'N') L
> =A0 FROM
> =A0 =A0 DUAL
> =A0 CONNECT BY
> =A0 =A0 LEVEL<=3D3),
> =A0 (SELECT
> =A0 =A0 TO_CHAR(ROWNUM-1) N
> =A0 FROM
> =A0 =A0 DUAL
> =A0 CONNECT BY
> =A0 =A0 LEVEL<=3D10));
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> Pankaj, mentioned that you were able to create a solution with
> TRANSLATE - please post that solution.
>
> 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.

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 --