Re: Excluding values with numbers
From: Pankaj <harpreet.noni_at_gmail.com>
Date: Thu, 31 Dec 2009 15:19:48 -0800 (PST)
Message-ID: <64387ce3-e135-42ff-b135-00f83fe7683f_at_u41g2000yqe.googlegroups.com>
On Dec 31, 4:14 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 31, 3:30 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > On 31.12.2009 19:58, Pankaj wrote:
>
> > > On Dec 30, 4:49 pm, Charles Hooper<hooperc2..._at_yahoo.com> wrote:
> > >> On Dec 30, 12:28 pm, joel garry<joel-ga..._at_home.com> wrote:
>
> > >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2..._at_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 -
Date: Thu, 31 Dec 2009 15:19:48 -0800 (PST)
Message-ID: <64387ce3-e135-42ff-b135-00f83fe7683f_at_u41g2000yqe.googlegroups.com>
On Dec 31, 4:14 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 31, 3:30 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
>
>
> > On 31.12.2009 19:58, Pankaj wrote:
>
> > > On Dec 30, 4:49 pm, Charles Hooper<hooperc2..._at_yahoo.com> wrote:
> > >> On Dec 30, 12:28 pm, joel garry<joel-ga..._at_home.com> wrote:
>
> > >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2..._at_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 Received on Thu Dec 31 2009 - 17:19:48 CST