Re: Excluding values with numbers

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 31 Dec 2009 13:14:00 -0800 (PST)
Message-ID: <2a3f7009-12a8-45bb-a42f-6f87610f6a19_at_26g2000yqo.googlegroups.com>

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. Received on Thu Dec 31 2009 - 15:14:00 CST