Excluding values with numbers
Pankaj
I am currently using Oracle9i Enterprise Edition Release 9.2.0.4.0. I
have a table with following data
Table 1 (Sample data)
a12345
A123423
g13452
G452323
h34423
r34323
b23232
n232323
I am currently using this as a subquery in one of the query. As per a
new request I have to now exclude all values which start with h, b or
n followed by numeric values.
So end result the subquery should give me is
Table 1 (Sample data)
a12345
A123423
g13452
G452323
r34323
I am little stumped on this for now. Could not get it right in my
query. Can anyone please advise here. Let me know if any more
information is needed from my side.
Note: The starting character in all values can sometimes in "lower
case" or sometimes in "upper case".
TIA
joel garry
Some people prefer that you post create table, insert data and what
sql statements you've tried, so they can quickly ramp up a test
environment. Is this school or work? (If school, people are usually
willing to give you hints on how to figure it out, not do it for you).
You could substr, uppercase and notinlist for the first character in
your where statement, and there are several ways to check the rest for
numeric, like http://www.adp-gmbh.ch/ora/plsql/helpers/is_numeric.html
jg
--
@home.com is bogus.
Two weeks... http://it.slashdot.org/story/09/12/29/1435259/Adobe-Flash-To-Be-Top-Hacker-Target-In-2010?art_pos=1
Carlos
I've used TRANSLATE() a lot in tasks similar to the one described by
op.
You may want to take a look at it.
HTH.
Cheers.
Carlos.
Charles Hooper
I agree with Joel's comments. Let's see if there is a hard way to do
this.
CREATE TABLE T10(HOMEWORK VARCHAR2(20));
INSERT INTO T10 VALUES ('a12345');
INSERT INTO T10 VALUES ('A123423');
INSERT INTO T10 VALUES ('g13452');
INSERT INTO T10 VALUES ('G452323');
INSERT INTO T10 VALUES ('h34423');
INSERT INTO T10 VALUES ('r34323');
INSERT INTO T10 VALUES ('b23232');
INSERT INTO T10 VALUES ('n232323');
INSERT INTO T10 VALUES ('NB151517');
INSERT INTO T10 VALUES ('C0151517');
INSERT INTO T10 VALUES ('f9151517');
INSERT INTO T10 VALUES ('HE4423');
COMMIT;
Note that I added a couple of extra rows just for fun.
Let's look at the ASCII values of the first and second characters:
SELECT
HOMEWORK,
ASCII(SUBSTR(HOMEWORK,1,1)) ASC_VAL1,
ASCII(SUBSTR(HOMEWORK,2,1)) ASC_VAL2
FROM
T10;
HOMEWORK ASC_VAL1 ASC_VAL2
---------- ---------- ----------
a12345 97 49
A123423 65 49
g13452 103 49
G452323 71 52
h34423 104 51
r34323 114 51
b23232 98 50
n232323 110 50
NB151517 78 66
C0151517 67 48
f9151517 102 57
HE4423 72 69
OK, I see the ones that we want to exclude, let's build a matrix:
SELECT
HOMEWORK,
ASCII(SUBSTR(HOMEWORK,1,1)) ASC_VAL1,
ASCII(SUBSTR(HOMEWORK,2,1)) ASC_VAL2,
DECODE(ASCII(SUBSTR(HOMEWORK,1,1)),
104,1,72,1,66,1,98,1,78,1,110,1,0) IS_EXC1,
DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
(SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
FROM
T10;
HOMEWORK ASC_VAL1 ASC_VAL2 IS_EXC1 IS_EXC2
---------- ---------- ---------- ---------- ----------
a12345 97 49 0 1
A123423 65 49 0 1
g13452 103 49 0 1
G452323 71 52 0 1
h34423 104 51 1 1
r34323 114 51 0 1
b23232 98 50 1 1
n232323 110 50 1 1
NB151517 78 66 1 0
C0151517 67 48 0 1
f9151517 102 57 0 1
HE4423 72 69 1 0
If there is a 1 in both of the right-most columns, then the row should
be eliminated. What is the easiest way to tell if there is a 1 in
both columns? Multiply the column values together, and if we receive
a product of 1 then the row should be excluded:
SELECT
*
FROM
(SELECT
HOMEWORK,
ASCII(SUBSTR(HOMEWORK,1,1)) ASC_VAL1,
ASCII(SUBSTR(HOMEWORK,2,1)) ASC_VAL2,
DECODE(ASCII(SUBSTR(HOMEWORK,1,1)),
104,1,72,1,66,1,98,1,78,1,110,1,0) IS_EXC1,
DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
(SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
FROM
T10)
WHERE
IS_EXC1*IS_EXC2<>1;
HOMEWORK ASC_VAL1 ASC_VAL2 IS_EXC1 IS_EXC2
---------- ---------- ---------- ---------- ----------
a12345 97 49 0 1
A123423 65 49 0 1
g13452 103 49 0 1
G452323 71 52 0 1
r34323 114 51 0 1
NB151517 78 66 1 0
C0151517 67 48 0 1
f9151517 102 57 0 1
HE4423 72 69 1 0
Something tells me you want to do it the easy way. See if you can do
anything with these functions:
REGEXP_INSTR
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions129.htm
REGEXP_SUBSTR
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions131.htm
*Always* post the DDL and DML to re-create your problem, and show us
what you have tried previously.
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.
joel garry
> On Dec 29, 11:54 am, Pankaj <harpreet.n...@gmail.com> wrote:
>
>
>
> > Greetings,
>
> > I am currently using Oracle9i Enterprise Edition Release 9.2.0.4.0. I
>
> I agree with Joel's comments. Let's see if there is a hard way to do
> this.
>
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
--
@home.com is bogus.
http://arstechnica.com/web/news/2009/09/horrifically-bad-software-demos-become-performance-art.ars
Charles Hooper
> On Dec 30, 5:31 am, Charles Hooper <hooperc2...@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.ppt
http://download.oracle.com/owsf_2003/40105.doc
Pankaj
> On Dec 30, 12:28 pm, joel garry <joel-ga...@home.com> wrote:
>
>
>
>
>
> > On Dec 30, 5:31 am, Charles Hooper <hooperc2...@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
>
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"http://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
>
> - 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.
Maxim Demenko
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
Charles Hooper
> 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
>
The numbers 0 through 9 have ASCII values ranging from 48 to 57.
* Obtain the second character in the column: SUBSTR(HOMEWORK,2,1)
* Use the ASCII function to find the ASCII value of the second
character
* Subtract 47 from the ASCII value for the second character
* If the difference is greater than 0, then:
** Subtract 58 from that ASCII value
** If the difference is less than 0, then we found an ASCII value
between 48 and 57 - therefore the second character must be a number
*** Return the number 1 if the ASCII value is between 48 and 57,
otherwise return 0
A CASE structure could be used rather than the cumbersome nested
DECODE and SIGN statements. A CASE structure will be easier to
maintain:
SELECT
CASE WHEN ASCII(SUBSTR(HOMEWORK,2,1)) >= 48
AND ASCII(SUBSTR(HOMEWORK,2,1)) <= 57 THEN 1
ELSE 0 END IS_EXC2
FROM
T10;
You could transform this section to a CASE structure also:
DECODE(ASCII(SUBSTR(HOMEWORK,1,1)),104,1,72,1,66,1,98,1,78,1,110,1,0)
IS_EXC1
SELECT
CASE ASCII(SUBSTR(HOMEWORK,1,1))
WHEN 104 THEN 1
WHEN 72 THEN 1
WHEN 66 THEN 1
WHEN 98 THEN 1
WHEN 78 THEN 1
WHEN 110 THEN 1
ELSE 0 END IS_EXC1
FROM
T10;
Finally, you could combine the two CASE structures in the WHERE
clause:
SELECT
HOMEWORK,
ASCII(SUBSTR(HOMEWORK,1,1)) ASC_VAL1,
ASCII(SUBSTR(HOMEWORK,2,1)) ASC_VAL2
FROM
T10
WHERE
(CASE ASCII(SUBSTR(HOMEWORK,1,1))
WHEN 104 THEN 1
WHEN 72 THEN 1
WHEN 66 THEN 1
WHEN 98 THEN 1
WHEN 78 THEN 1
WHEN 110 THEN 1
ELSE 0 END) *
(CASE WHEN ASCII(SUBSTR(HOMEWORK,2,1)) >= 48
AND ASCII(SUBSTR(HOMEWORK,2,1)) <= 57 THEN 1
ELSE 0 END) = 0;
HOMEWORK ASC_VAL1 ASC_VAL2
---------- ---------- ----------
a12345 97 49
A123423 65 49
g13452 103 49
G452323 71 52
r34323 114 51
NB151517 78 66
C0151517 67 48
f9151517 102 57
HE4423 72 69
There are probably several other ways to solve this problem.
Charles Hooper
> On 31.12.2009 19:58, Pankaj wrote:
>
>
>
>
>
> > On Dec 30, 4:49 pm, Charles Hooper<hooperc2...@yahoo.com> wrote:
> >> On Dec 30, 12:28 pm, joel garry<joel-ga...@home.com> wrote:
>
> >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2...@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
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. :-)
Pankaj
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
Charles Hooper
> 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.
ddf
>
> - 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
Mark D Powell
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 --
Charles Hooper
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.
Pankaj
>
> - Show quoted text -
Thanks Everyone. I have got more than what I expected. I would like to
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.