How to check if a string contains only number and alphabetic characters?

Hi, guys,

I try to write a stored procedure to check if a string column contains specifial characters, I loop through each row of the table, then loop through each letter of the string, then do the following compare:

IF

(ascii(SUBSTR(string1, i, 1)) <= 47)
OR
(ascii(SUBSTR(string1, i, 1)) >= 58 AND ascii(SUBSTR(string1, i, 1)) <= 64)
OR
(ascii(SUBSTR(string1, i, 1)) >= 91 AND ascii(SUBSTR(string1, i, 1)) <= 96)
OR
(ascii(SUBSTR(string1, i, 1)) >= 123 AND ascii(SUBSTR(string1, i, 1)) <= 127)

THEN

it is a string contains special characters

END IF

But the performance is very bad because this table is huge, is there another way to do this?

Thanks

Hi

Have you narrowed down the list of special characters ?
if yes then you can do a couple of things

1)Upgrade to 10g and use its regex feautures

2)Write a Java or a C regex function and call that from
within sql



regards
Hrishy

Code:

---

IF TRANSLATE (UPPER(l_string),
              '#0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
              '#') IS NOT NULL
THEN

it is a string contains special characters

END IF

---