Re: Excluding values with numbers
From: Pankaj <harpreet.noni_at_gmail.com>
Date: Thu, 31 Dec 2009 10:58:11 -0800 (PST)
Message-ID: <f76fe411-a96a-4cce-a887-f24cea271ea7_at_m3g2000yqf.googlegroups.com>
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://download.oracle.com/owsf_2003/40105.doc
>
> 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 10:58:11 -0800 (PST)
Message-ID: <f76fe411-a96a-4cce-a887-f24cea271ea7_at_m3g2000yqf.googlegroups.com>
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://download.oracle.com/owsf_2003/40105.doc
>
> 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. Received on Thu Dec 31 2009 - 12:58:11 CST