Re: Excluding values with numbers
Date: Fri, 1 Jan 2010 14:13:31 -0800 (PST)
Message-ID: <0e49ac71-da98-4db6-879b-c6cbab7e3cbc_at_r24g2000yqd.googlegroups.com>
On Jan 1, 12:13 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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
Nice example with the TRANSLATE function.
If the OP were running Oracle 10g R1 or later the following would also
work:
(REGEXP_INSTR)
SELECT
HOMEWORK
FROM
T10
WHERE
REGEXP_INSTR(UPPER(HOMEWORK),'[HBN][0123456789]')<>1;
HOMEWORK
a12345
A123423
g13452
G452323
r34323
NB151517
C0151517
f9151517
HE4423
(Shortened version of the above)
SELECT
HOMEWORK
FROM
T10
WHERE
REGEXP_INSTR(UPPER(HOMEWORK),'[HBN][0-9]')<>1
HOMEWORK
a12345
A123423
g13452
G452323
r34323
NB151517
C0151517
f9151517
HE4423
(REGEXP_REPLACE)
SELECT
HOMEWORK
FROM
T10
WHERE
REGEXP_REPLACE(SUBSTR(UPPER(HOMEWORK),1,2),'[HBN][0123456789]',NULL)
IS NOT NULL;
HOMEWORK
a12345
A123423
g13452
G452323
r34323
NB151517
C0151517
f9151517
HE4423
There must be a couple more ways to solve this SQL problem.
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 Fri Jan 01 2010 - 16:13:31 CST