Excluding values with numbers [Oracle Help]

Prev: oracle and database ebooks
Next: Oracle 10g Lite & .NET

From: Pankaj on 31 Dec 2009 18:19

On Dec 31, 4:14 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> On Dec 31, 3:30 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote:
>
>
>
>
>
> > On 31.12.2009 19:58, Pankaj wrote:
>
> > > On Dec 30, 4:49 pm, Charles Hooper<hooperc2...(a)yahoo.com> wrote:
> > >> On Dec 30, 12:28 pm, joel garry<joel-ga...(a)home.com> wrote:
>
> > >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2...(a)yahoo.com> wrote:
> > >>> LOL, you should write a book! "Bad SQL! Bad, bad!"
>
> > >>>> Something tells me you want to do it the easy way. See if you can do
> > >>>> anything with these functions:
> > >>>> REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > >>>> REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > >>>> *Always* post the DDL and DML to re-create your problem, and show us
> > >>>> what you have tried previously.
>
> > >>> Watch those versions :-)
>
> > >>> (And thanks Carlos, I should've thought of that first. TIMTOWTDI)
>
> > >>> jg
>
> > >> That would be an interesting title for a book. Take a somewhat simple
> > >> request and see how many different (or overly complex) solutions may
> > >> be generated for the request.
>
> > >> More specifically on your second point, regular expressions are not
> > >> available in Oracle 9i R2 - for some reason I thought that they were
> > >> introduced with Oracle 9i R1 (I even performed a search to verify - I
> > >> should have clicked one of the links). After seeing your post, I
> > >> searched again and found a couple interesting articles for those
> > >> people running Oracle 10g R1 and above:http://download.oracle.com/owsf_2003/40105_Gennick_04.ppthttp://downl...
>
> > >> 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.- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Thanks Everyone.
>
> > > Carlos/Joe: I tried TRANSLATE option and it works.
> > > Charles: I will go ahead with your option for now. Can you please
> > > detail me on what the below expression is doing.
>
> > > DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
> > > (SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
>
> > > TIA.
>
> > It checks, whether the second character in the column HOMEWORK
> > represents a digit. You can look at the results of the query
> > with t as (
> > select chr(32)||chr(rownum + 31) c from dual
> > connect by level <= 128-32
> > )
> > select c,
> > decode(sign(ascii(substr(c,2,1))-47),1,decode(sign(ascii
> > (substr(c,2,1))-58),-1,1,0),0) is_exc2
> > from t
>
> > Just to mention another approach regarding your question:
>
> > SQL> with t as (
> > 2 select 'a12345' c from dual union all
> > 3 select 'A123423' from dual union all
> > 4 select 'g13452' from dual union all
> > 5 select 'G452323' from dual union all
> > 6 select 'h34423' from dual union all
> > 7 select 'r34323' from dual union all
> > 8 select 'b23232' from dual union all
> > 9 select 'n' from dual union all
> > 10 select 'n232323' from dual
> > 11 )
> > 12 -- End test data
> > 13 select c
> > 14 from t
> > 15 where not lower(rtrim(c,'0123456789')) in ('h','b','n')
> > 16 /
>
> > C
> > -------
> > a12345
> > A123423
> > g13452
> > G452323
> > r34323
>
> > Best regards
>
> > Maxim
>
> Nice solution! I did not even think of using RTRIM to strip off the
> characters at the right of the string when those characters are found
> in the string. You did not even need to divide by 0 to produce the
> desired result. :-)
>
> 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.- Hide quoted text -
>
> - Show quoted text -

Charles: Thanks for the detailed explanation. And yes, with CASE
statement, its much easier to understand and maintain.

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

From: Charles Hooper on 31 Dec 2009 22:34

On Dec 31, 6:19 pm, Pankaj <harpreet.n...(a)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.

From: ddf on 1 Jan 2010 11:57

On Dec 31 2009, 6:19 pm, Pankaj <harpreet.n...(a)gmail.com> wrote:
> On Dec 31, 4:14 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
>
>
>
>
>
> > On Dec 31, 3:30 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote:
>
> > > On 31.12.2009 19:58, Pankaj wrote:
>
> > > > On Dec 30, 4:49 pm, Charles Hooper<hooperc2...(a)yahoo.com> wrote:
> > > >> On Dec 30, 12:28 pm, joel garry<joel-ga...(a)home.com> wrote:
>
> > > >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2...(a)yahoo.com> wrote:
> > > >>> LOL, you should write a book! "Bad SQL! Bad, bad!"
>
> > > >>>> Something tells me you want to do it the easy way. See if you can do
> > > >>>> anything with these functions:
> > > >>>> REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > > >>>> REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server..102/b14200/functi...
>
> > > >>>> *Always* post the DDL and DML to re-create your problem, and show us
> > > >>>> what you have tried previously.
>
> > > >>> Watch those versions :-)
>
> > > >>> (And thanks Carlos, I should've thought of that first. TIMTOWTDI)
>
> > > >>> jg
>
> > > >> That would be an interesting title for a book. Take a somewhat simple
> > > >> request and see how many different (or overly complex) solutions may
> > > >> be generated for the request.
>
> > > >> More specifically on your second point, regular expressions are not
> > > >> available in Oracle 9i R2 - for some reason I thought that they were
> > > >> introduced with Oracle 9i R1 (I even performed a search to verify - I
> > > >> should have clicked one of the links). After seeing your post, I
> > > >> searched again and found a couple interesting articles for those
> > > >> people running Oracle 10g R1 and above:http://download.oracle.com/owsf_2003/40105_Gennick_04.ppthttp://downl...
>
> > > >> 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.- Hide quoted text -
>
> > > >> - Show quoted text -
>
> > > > Thanks Everyone.
>
> > > > Carlos/Joe: I tried TRANSLATE option and it works.
> > > > Charles: I will go ahead with your option for now. Can you please
> > > > detail me on what the below expression is doing.
>
> > > > DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
> > > > (SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
>
> > > > TIA.
>
> > > It checks, whether the second character in the column HOMEWORK
> > > represents a digit. You can look at the results of the query
> > > with t as (
> > > select chr(32)||chr(rownum + 31) c from dual
> > > connect by level <= 128-32
> > > )
> > > select c,
> > > decode(sign(ascii(substr(c,2,1))-47),1,decode(sign(ascii
> > > (substr(c,2,1))-58),-1,1,0),0) is_exc2
> > > from t
>
> > > Just to mention another approach regarding your question:
>
> > > SQL> with t as (
> > > 2 select 'a12345' c from dual union all
> > > 3 select 'A123423' from dual union all
> > > 4 select 'g13452' from dual union all
> > > 5 select 'G452323' from dual union all
> > > 6 select 'h34423' from dual union all
> > > 7 select 'r34323' from dual union all
> > > 8 select 'b23232' from dual union all
> > > 9 select 'n' from dual union all
> > > 10 select 'n232323' from dual
> > > 11 )
> > > 12 -- End test data
> > > 13 select c
> > > 14 from t
> > > 15 where not lower(rtrim(c,'0123456789')) in ('h','b','n')
> > > 16 /
>
> > > C
> > > -------
> > > a12345
> > > A123423
> > > g13452
> > > G452323
> > > r34323
>
> > > Best regards
>
> > > Maxim
>
> > Nice solution! I did not even think of using RTRIM to strip off the
> > characters at the right of the string when those characters are found
> > in the string. You did not even need to divide by 0 to produce the
> > desired result. :-)
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Charles: Thanks for the detailed explanation. And yes, with CASE
> statement, its much easier to understand and maintain.
>
> 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- Hide quoted text -
>
> - Show quoted text -

No, you don't as Maxim's solution works regardless:

SQL> with t as (
2 select 'a12345' c from dual union all
3 select 'A123423' from dual union all
4 select 'g13452' from dual union all
5 select 'G452323' from dual union all
6 select 'h34423' from dual union all
7 select 'r34323' from dual union all
8 select 'b23232' from dual union all
9 select 'n' from dual union all
10 select 'n232323' from dual union all
11 select 'hhhhhhh' from dual
12 )
13 -- End test data
14 select c
15 from t
16 where not lower(rtrim(c,'0123456789')) in ('h','b','n');

C
-------
a12345
A123423
g13452
G452323
r34323
hhhhhhh

6 rows selected.

SQL>

David Fitzjarrell

From: Mark D Powell on 1 Jan 2010 12:13

On Dec 31 2009, 10:34 pm, Charles Hooper <hooperc2...(a)yahoo.com>
wrote:
> On Dec 31, 6:19 pm, Pankaj <harpreet.n...(a)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 --

From: Charles Hooper on 1 Jan 2010 17:13

On Jan 1, 12:13 pm, Mark D Powell <Mark.Powe...(a)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.

First | Prev | Next | Last
Pages: 1 2 3 4
Prev: oracle and database ebooks
Next: Oracle 10g Lite & .NET