Re: Excluding values with numbers

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 31 Dec 2009 21:30:37 +0100
Message-ID: <4B3D09ED.3070404_at_gmail.com>

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://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.

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 Received on Thu Dec 31 2009 - 14:30:37 CST