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

[zxmgh]

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

[hrishy]

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

[jmodic]

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

it is a string contains special characters

END IF