non alphanumeric characters

Hello,

I want to replace all the non alphanumeric characters with 0 in a given string. Could any one help me with it? I can use function "replace" to replace a certain sign ( for example '+' )
with 0. But how could I find all the non alphanumeric characters once? Is there a Oracle function for this?

Thanks.

You can use TRANSLATE fruncion.

TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that
are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end
of from_string have no corresponding characters in to_string. If these extra characters appear in char, they are removed from the return value.

But how could I find all the non alphanumeric characters( such as $, +, #, 4, 2,^, ?, @ etc), not only one or two?

Is there an Oracle function to do this job, like [^a-zA-Z] in perl?

Thanks.

Little example :

translate(source_str,
'&^%$#@!*() asdfhgkj...',
'0000000000 asdfhgkj...');

Thanks, Shestakov.

But I still do not want to hard code these signs. Any idea?

Little bit more example:

declare
str_template varchar2(256);
str_translate varchar2(256);
begin
..
str_template := some_create_tmpl(source_str, ...);
str_translate := some_create_pattern(source_str, ...);
..

res_string := translate(source_str,
str_template,
str_template);

..
end;

That means Oracle does not have a function to search for all the non alphanumeric characters, right?

Yes, it's right.

Thanks, Shestakov.