Re: Excluding values with numbers

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 31 Dec 2009 13:04:57 -0800 (PST)
Message-ID: <c55cc487-54d6-44c2-8ee5-4deee8655837_at_35g2000yqa.googlegroups.com>

On Dec 31, 1:58 pm, Pankaj <harpreet.n..._at_gmail.com> wrote:
> 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
>

The numbers 0 through 9 have ASCII values ranging from 48 to 57. * Obtain the second character in the column: SUBSTR(HOMEWORK,2,1) * Use the ASCII function to find the ASCII value of the second character

* Subtract 47 from the ASCII value for the second character
* If the difference is greater than 0, then:
** Subtract 58 from that ASCII value
** If the difference is less than 0, then we found an ASCII value

between 48 and 57 - therefore the second character must be a number *** Return the number 1 if the ASCII value is between 48 and 57, otherwise return 0

A CASE structure could be used rather than the cumbersome nested DECODE and SIGN statements. A CASE structure will be easier to maintain:
SELECT
  CASE WHEN ASCII(SUBSTR(HOMEWORK,2,1)) >= 48         AND ASCII(SUBSTR(HOMEWORK,2,1)) <= 57 THEN 1     ELSE 0 END IS_EXC2
FROM
  T10;

You could transform this section to a CASE structure also: DECODE(ASCII(SUBSTR(HOMEWORK,1,1)),104,1,72,1,66,1,98,1,78,1,110,1,0) IS_EXC1 SELECT
  CASE ASCII(SUBSTR(HOMEWORK,1,1))
    WHEN 104 THEN 1
    WHEN 72 THEN 1
    WHEN 66 THEN 1
    WHEN 98 THEN 1
    WHEN 78 THEN 1
    WHEN 110 THEN 1
    ELSE 0 END IS_EXC1
FROM
  T10;

Finally, you could combine the two CASE structures in the WHERE clause:
SELECT
  HOMEWORK,
  ASCII(SUBSTR(HOMEWORK,1,1)) ASC_VAL1,
  ASCII(SUBSTR(HOMEWORK,2,1)) ASC_VAL2
FROM
  T10
WHERE
  (CASE ASCII(SUBSTR(HOMEWORK,1,1))
    WHEN 104 THEN 1
    WHEN 72 THEN 1
    WHEN 66 THEN 1
    WHEN 98 THEN 1
    WHEN 78 THEN 1
    WHEN 110 THEN 1
    ELSE 0 END) *
  (CASE WHEN ASCII(SUBSTR(HOMEWORK,2,1)) >= 48         AND ASCII(SUBSTR(HOMEWORK,2,1)) <= 57 THEN 1     ELSE 0 END) = 0; HOMEWORK ASC_VAL1 ASC_VAL2
---------- ---------- ----------

a12345             97         49
A123423            65         49
g13452            103         49
G452323            71         52
r34323            114         51
NB151517           78         66
C0151517           67         48
f9151517          102         57
HE4423             72         69

There are probably several other ways to solve this problem.

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:04:57 CST