non alphanumeric characters
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: non alphanumeric characters

  1. #1
    Join Date
    Jul 2000
    Posts
    68
    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.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.


  3. #3
    Join Date
    Jul 2000
    Posts
    68
    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.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Little example :

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

  5. #5
    Join Date
    Jul 2000
    Posts
    68
    Thanks, Shestakov.

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

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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;

  7. #7
    Join Date
    Jul 2000
    Posts
    68
    That means Oracle does not have a function to search for all the non alphanumeric characters, right?


  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Yes, it's right.

  9. #9
    Join Date
    Jul 2000
    Posts
    68
    Thanks, Shestakov.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width