non alphanumeric characters

[wwalice]

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.

[Shestakov]

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.

[wwalice]

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.

[Shestakov]

Little example :

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

[wwalice]

Thanks, Shestakov.

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

[Shestakov]

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;

[wwalice]

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

[Shestakov]

Yes, it's right.

[wwalice]

Thanks, Shestakov.