replace和translate

REPLACE :
 replace function is:

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string

For example:
 

replace('123123tech', '123');	would return 'tech'
replace('123tech123', '123');	would return 'tech'
replace('222tech', '2', '3');	would return '333tech'

:: extact one to one matching of character string like '123' or 'abc'
   replacement character is not needed.

Translate :

translate( string1, string_to_replace, replacement_string )

 

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

For example:

translate('1tech23', '123', '456');	would return '4tech56'
translate('222tech', '2ec', '3it');	would return '333tith'

:: extact one to on e matching of character like 'a' or '1'
   replace character is needed
select translate('abc132fdsf','0123456789',' ') from dual;

 

 

-------

abcfdsf

或按以下方式

SQL> select translate('abc132fdsf','#0123456789','#') from dual;

 

-------

abcfdsf

--提取字符串中的数字，字母都被过滤，如下：

 

SQL> select translate('abc132fdsf','abcdefghigklmnopqrstuvwxyz',' ') from dual;

 

 

----

132