# Re: Excluding values with numbers

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 31 Dec 2009 19:34:40 -0800 (PST)

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

Original text of this message