Path: g2news1.google.com!postnews.google.com!u41g2000yqe.googlegroups.com!not-for-mail
From: Pankaj <harpreet.n...@gmail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Excluding values with numbers
Date: Thu, 31 Dec 2009 15:19:48 -0800 (PST)
Organization: http://groups.google.com
Lines: 134
Message-ID: <64387ce3-e135-42ff-b135-00f83fe7683f@u41g2000yqe.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> 
	<f76fe411-a96a-4cce-a887-f24cea271ea7@m3g2000yqf.googlegroups.com> 
	<4B3D09ED.3070404@gmail.com> <2a3f7009-12a8-45bb-a42f-6f87610f6a19@26g2000yqo.googlegroups.com>
NNTP-Posting-Host: 205.181.240.194
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1262301588 24104 127.0.0.1 (31 Dec 2009 23:19:48 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 31 Dec 2009 23:19:48 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: u41g2000yqe.googlegroups.com; posting-host=205.181.240.194; 
	posting-account=Z_-z9AoAAAAY7-4g7g4HhkN6T9YzCB4M
User-Agent: G2/1.0
X-HTTP-Via: 1.0 nowbdcpx01b.statestr.com:80 (IronPort-WSA/5.6.2-102)
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET 
	CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 
	3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe)

On Dec 31, 4:14=A0pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On Dec 31, 3:30=A0pm, Maxim Demenko <mdeme...@gmail.com> wrote:
>
>
>
>
>
> > On 31.12.2009 19:58, Pankaj wrote:
>
> > > On Dec 30, 4:49 pm, Charles Hooper<hooperc2...@yahoo.com> =A0wrote:
> > >> On Dec 30, 12:28 pm, joel garry<joel-ga...@home.com> =A0wrote:
>
> > >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2...@yahoo.com> =A0wrote:
> > >>> 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 c=
an do
> > >>>> anything with these functions:
> > >>>> REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.10=
2/b14200/functi...
>
> > >>>> REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server.1=
02/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. =A0Take a somewhat si=
mple
> > >> 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). =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/ow=
sf_2003/40105_Gennick_04.ppthttp://downl...
>
> > >> Charles Hooper
> > >> Co-author of "Expert Oracle Practices: Oracle Database Administratio=
n
> > >> 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:  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