# Re: Excluding values with numbers

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 31 Dec 2009 13:04:57 -0800 (PST)

On Dec 31, 1:58 pm, Pankaj <harpreet.n..._at_gmail.com> wrote:
> Thanks Everyone.
>
> Carlos/Joe: I tried TRANSLATE option and it works.
> 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

Original text of this message