Excluding values with numbers
in [Oracle Help]
|
Prev: oracle and database ebooks
Next: Oracle 10g Lite & .NET
From: Pankaj on 29 Dec 2009 11:54 Greetings, 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
From: joel garry on 29 Dec 2009 12:30 On Dec 29, 8:54 am, Pankaj <harpreet.n...(a)gmail.com> wrote: > Greetings, > > 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 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
From: Carlos on 30 Dec 2009 07:02 On Dec 29, 6:30 pm, joel garry <joel-ga...(a)home.com> wrote: > On Dec 29, 8:54 am, Pankaj <harpreet.n...(a)gmail.com> wrote: > > > > > Greetings, > > > 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 > > 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, likehttp://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-H... 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.
From: Charles Hooper on 30 Dec 2009 08:31 On Dec 29, 11:54 am, Pankaj <harpreet.n...(a)gmail.com> wrote: > Greetings, > > 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 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.
From: joel garry on 30 Dec 2009 12:28
On Dec 30, 5:31 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Dec 29, 11:54 am, Pankaj <harpreet.n...(a)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 |