Re: Excluding values with numbers
Date: Thu, 31 Dec 2009 19:34:40 -0800 (PST)
Message-ID: <94349667-27f9-440f-9c51-dfe2214c079b_at_c3g2000yqd.googlegroups.com>
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.
Received on Thu Dec 31 2009 - 21:34:40 CST