Brief Analysis of replacement functions in oracle

Brief Analysis of replacement functions in oracle

Replace is a string replacement in the general sense.

Replace (c1, c2 [, c3])

Find string c2 in string c1 and replace it with c3. If c3 is null, all C2.

update jd set id = replace(id,'1','2');

It means to replace all 1 in the id column of the jd table with 2.

Translate is only a character to replace.

TRANSLATE (expr, from_string, to_string)

To put it simply, replace the characters in from_string with the characters in to_string for the expr content.

select translate('123456789','456','abc') from dual;    123abc789select translate('123456789','456','ab') from dual;    123ab789select translate('123456789','4564','a') from dual;    123a789select translate('123456789','4564','abcd') from dual;    123abc789select TRANSLATE('kkaxksx', 'kx', '12') from dual         11a21s2   

Brief comparison:

Replace string-level replacement

For example, select replace ('accd', 'cd', 'ef ') from dual; --> aefd



Replacement of the translate character level

For example, select translate ('acdd', 'cd', 'ef ') from dual; --> aeff

In translate, there is a special usage of "#", which indicates all characters starting "#".

The main function of translate is to extract and replace strings. Its function is sometimes similar to replace. For more information, see the following example.

select translate('liyan4h123ui','#liyanhui','#') from dual       4123         select translate('liyan4h123ui','#liyanhui','#z') from dual;       z4123         select translate('liyan4h123ui','#liyanhui','#zx') from dual;       zx4123x         select translate('asadad434323', '#0123456789','#') from dual ;      asadad  

Use TRANSLATE to filter keywords

Sometimes it is necessary to filter some key words and directly use replace, because these key words are too many to be nested for use, the statement is not easy to write, but also a waste of resources. In this case, you can use the combination of TRANSLATE and replace to completely achieve the goal.

For example, if you want to use "Shenzhen" or "Beijing" as key words, filter these words out and do not display them in the displayed content:

-- Use TRANSLATE to convert key words into a special string. For example, X select TRANSLATE ('shanghai Beijing Tianjin Chongqing Guangzhou Wuhan ', 'shenzhen Beijing ', 'xxxx') from dual; Shanghai XX Tianjin Chongqing Guangzhou XX Wuhan select replace (TRANSLATE ('shanghai Beijing Tianjin Chongqing Guangzhou Wuhan ', 'shenzhen Beijing', 'xxxx '), 'X') from dual; Shanghai Tianjin Chongqing Guangzhou Wuhan select TRANSLATE ('shanghai Beijing Tianjin Chongqing Beijing Wuhan ', 'shenzhen Beijing', 'xxxx') from dual; shanghai X Tianjin Chongqing X Guangzhou XX Wuhan