Re: Excluding values with numbers
Date: Fri, 1 Jan 2010 09:13:03 -0800 (PST)
Message-ID: <948e0e28-9efe-4902-9ceb-de2c1ee8b93b_at_k19g2000yqc.googlegroups.com>
On Dec 31 2009, 10:34 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 31, 6:19 pm, Pankaj <harpreet.n..._at_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. Here is an explanation of his
> solution:
> SELECT
> *
> FROM
> 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
> HOMEWORK,
> RTRIM(HOMEWORK,'0123456789') TEST
> FROM
> T10;
>
> HOMEWORK TEST
> ---------- ----
> a12345 a
> A123423 A
> g13452 g
> G452323 G
> h34423 h
> r34323 r
> b23232 b
> n232323 n
> NB151517 NB
> C0151517 C
> f9151517 f
> HE4423 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. Then, 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
> HOMEWORK
> FROM
> T10
> WHERE
> 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
> HOMEWORK
> FROM
> T10
> MINUS
> SELECT
> HOMEWORK
> FROM
> T10
> WHERE
> UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> 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
> HOMEWORK
> FROM
> T10
> MINUS
> SELECT
> HOMEWORK
> FROM
> T10
> WHERE
> UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> AND SUBSTR(HOMEWORK,2,1) IN (
> SELECT
> TO_CHAR(ROWNUM-1)
> FROM
> DUAL
> CONNECT BY
> LEVEL<=10);
>
> HOMEWORK
> --------
> A123423
> C0151517
> G452323
> HE4423
> NB151517
> a12345
> f9151517
> g13452
> r34323
>
> The NOT method:
> SELECT
> HOMEWORK
> FROM
> T10
> WHERE
> NOT(UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> 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
> HOMEWORK
> FROM
> T10
> WHERE
> NOT(UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N')
> AND SUBSTR(HOMEWORK,2,1) IN (
> SELECT
> TO_CHAR(ROWNUM-1)
> FROM
> DUAL
> CONNECT BY
> LEVEL<=10));
>
> HOMEWORK
> --------
> a12345
> A123423
> g13452
> G452323
> r34323
> NB151517
> C0151517
> f9151517
> HE4423
>
> The left outer join method:
> SELECT
> T10.HOMEWORK
> FROM
> T10,
> (SELECT
> HOMEWORK
> FROM
> T10
> WHERE
> (UPPER(SUBSTR(HOMEWORK,1,1)) IN ('H','B','N'))
> AND (SUBSTR(HOMEWORK,2,1) IN (
> SELECT
> TO_CHAR(ROWNUM-1)
> FROM
> DUAL
> CONNECT BY
> LEVEL<=10))) NT10
> WHERE
> T10.HOMEWORK=NT10.HOMEWORK(+)
> AND NT10.HOMEWORK IS NULL;
>
> HOMEWORK
> --------
> A123423
> C0151517
> r34323
> HE4423
> g13452
> f9151517
> a12345
> G452323
> NB151517
>
> The Cartesian join method:
> SELECT
> HOMEWORK
> FROM
> T10
> WHERE
> UPPER(SUBSTR(HOMEWORK,1,2)) NOT IN
> (SELECT
> L||N
> FROM
> (SELECT
> DECODE(ROWNUM,1,'H',2,'B',3,'N') L
> FROM
> DUAL
> CONNECT BY
> LEVEL<=3),
> (SELECT
> TO_CHAR(ROWNUM-1) N
> FROM
> DUAL
> CONNECT BY
> LEVEL<=10));
>
> 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 -- Received on Fri Jan 01 2010 - 11:13:03 CST