Common Oracle Function Records

1. Oracle 's replace function and translate function

The Replace function is substituted at the string level, corresponding to the string one by one substitution

Sql> SELECT REPLACE ('accd','cd','EF '  from dual; REPLACE ('ACCD','CD','  EF')-------------------------ACEF

translate function is substituted at the character level, corresponding to character one by one substitution    translate (expr,from_string,to_string)  

 sql> select  translate ( " ACDD  , "   , "   EF  ) from   dual; TRANSLATE (  ACDD  , "  CD  ,  '  ef   '   ' ---------------------------aeff  

Translate function is empty at to_string position, the return value is null

Select Translate ('acdd','cd', ' from dual; TRANSLATE ('acdd','CD', ' )

There are special usages of # in translate, which begin with # to denote all characters

Sql>SelectTranslate'Liyan4h123ui','#liyanhui','#') fromdual; TRANSLATE ('Liyan4h123ui','#LIY------------------------------4123SQL>SelectTranslate'Liyan4h123ui','#liyanhui','#z') fromdual; TRANSLATE ('Liyan4h123ui','#LIY------------------------------z4123 SQL>SelectTranslate'Liyan4h123ui','#liyanhui','#zx') fromdual; TRANSLATE ('Liyan4h123ui','#LIY------------------------------zx4123x SQL>SelectTranslate'asadad434323','#0123456789','#') fromdual; TRANSLATE ('ASADAD434323','#012------------------------------Asadad

------to Be Continued

Common Oracle Function Records