 | |
Excluding values with numbers
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Excluding values with numbers -
12-31-2009
, 05:19 PM
|
On Dec 31, 3:30*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote: On 31.12.2009 19:58, Pankaj wrote: On Dec 30, 4:49 pm, Charles Hooper<hooperc2... (AT) yahoo (DOT) com> *wrote: On Dec 30, 12:28 pm, joel garry<joel-ga... (AT) home (DOT) com> *wrote: On Dec 30, 5:31 am, Charles Hooper<hooperc2... (AT) yahoo (DOT) 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...pthttp://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 - |
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
#12
| |||
| |||
|
|
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 |
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.
#13
| |||
| |||
|
|
On Dec 31, 4:14*pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote: On Dec 31, 3:30*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote: On 31.12.2009 19:58, Pankaj wrote: On Dec 30, 4:49 pm, Charles Hooper<hooperc2... (AT) yahoo (DOT) com> *wrote: On Dec 30, 12:28 pm, joel garry<joel-ga... (AT) home (DOT) com> *wrote: On Dec 30, 5:31 am, Charles Hooper<hooperc2... (AT) yahoo (DOT) 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 youcan 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...pthttp://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 - |
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
#14
| |||
| |||
|
|
On Dec 31, 6:19*pm, Pankaj <harpreet.n... (AT) gmail (DOT) 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. |
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 --
#15
| |||
| |||
|
|
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 |
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.
#16
| |||
| |||
|
|
On Jan 1, 12:13*pm, Mark D Powell <Mark.Powe... (AT) hp (DOT) 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.- Hide quoted text - - Show quoted text - |
thanks everyone again.
Charles: I was able to work it out using TRANSLATE but have alredy
replaced with new solution given my others.
Thanks again.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.