Re: Excluding values with numbers

From: ddf <oratune_at_msn.com>
Date: Fri, 1 Jan 2010 08:57:37 -0800 (PST)
Message-ID: <4570a6c2-d16b-4df6-9951-a61cacdc6552_at_a32g2000yqm.googlegroups.com>



On Dec 31 2009, 6:19 pm, Pankaj <harpreet.n..._at_gmail.com> wrote:
> 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- 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 Received on Fri Jan 01 2010 - 10:57:37 CST

Original text of this message