Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!a32g2000yqm.googlegroups.com!not-for-mail From: ddf Newsgroups: comp.databases.oracle.misc Subject: Re: Excluding values with numbers Date: Fri, 1 Jan 2010 08:57:37 -0800 (PST) Organization: http://groups.google.com Lines: 178 Message-ID: <4570a6c2-d16b-4df6-9951-a61cacdc6552@a32g2000yqm.googlegroups.com> References: <5b741819-135e-4812-a75e-e9342954f42c@h10g2000vbm.googlegroups.com> <92f72abb-3a28-4f40-822c-431b928acf2b@o28g2000yqh.googlegroups.com> <895685f9-0295-439e-93f7-9b45e264f570@n38g2000yqf.googlegroups.com> <8c45e8d0-405f-4fad-9d18-6a0ccf5485e8@n38g2000yqf.googlegroups.com> <4B3D09ED.3070404@gmail.com> <2a3f7009-12a8-45bb-a42f-6f87610f6a19@26g2000yqo.googlegroups.com> <64387ce3-e135-42ff-b135-00f83fe7683f@u41g2000yqe.googlegroups.com> NNTP-Posting-Host: 72.192.72.65 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1262365057 2980 127.0.0.1 (1 Jan 2010 16:57:37 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 1 Jan 2010 16:57:37 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: a32g2000yqm.googlegroups.com; posting-host=72.192.72.65; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.2),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Dec 31 2009, 6:19=A0pm, Pankaj wrote: > On Dec 31, 4:14=A0pm, Charles Hooper wrote: > > > > > > > On Dec 31, 3:30=A0pm, Maxim Demenko wrote: > > > > On 31.12.2009 19:58, Pankaj wrote: > > > > > On Dec 30, 4:49 pm, Charles Hooper =A0wrote: > > > >> On Dec 30, 12:28 pm, joel garry =A0wrote: > > > > >>> On Dec 30, 5:31 am, Charles Hooper =A0wrot= e: > > > >>> LOL, you should write a book! =A0"Bad SQL! Bad, bad!" > > > > >>>> Something tells me you want to do it the easy way. =A0See 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 sho= w 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. =A0Take a somewhat = simple > > > >> request and see how many different (or overly complex) solutions m= ay > > > >> be generated for the request. > > > > >> More specifically on your second point, regular expressions are no= t > > > >> available in Oracle 9i R2 - for some reason I thought that they we= re > > > >> introduced with Oracle 9i R1 (I even performed a search to verify = - I > > > >> should have clicked one of the links). =A0After 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 Administrat= ion > > > >> 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 ( > > > =A0 select chr(32)||chr(rownum + 31) c from dual > > > =A0 connect by level <=3D 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 ( > > > =A0 =A02 =A0 select 'a12345' c from dual =A0union all > > > =A0 =A03 =A0 select 'A123423' from dual =A0union all > > > =A0 =A04 =A0 select 'g13452' from dual =A0union all > > > =A0 =A05 =A0 select 'G452323' from dual =A0union all > > > =A0 =A06 =A0 select 'h34423' from dual =A0union all > > > =A0 =A07 =A0 select 'r34323' from dual =A0union all > > > =A0 =A08 =A0 select 'b23232' from dual =A0union all > > > =A0 =A09 =A0 select 'n' from dual union all > > > =A0 10 =A0 select 'n232323' from dual > > > =A0 11 =A0) > > > =A0 12 =A0-- End test data > > > =A0 13 =A0select c > > > =A0 14 =A0from t > > > =A0 15 =A0where not lower(rtrim(c,'0123456789')) in ('h','b','n') > > > =A0 16 =A0/ > > > > C > > > ------- > > > a12345 > > > A123423 > > > g13452 > > > G452323 > > > r34323 > > > > Best regards > > > > Maxim > > > Nice solution! =A0I 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. =A0You did not even need to divide by 0 to produce the > > desired result. =A0:-) > > > 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: =A0Thanks 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 - 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