A brief analysis of substitution functions in Oracle

Replace is a string substitution in the general sense

Replace (C1,C2[,C3])

Find the string C2 in the string C1, replacing it with C3. If C3 is empty, all C2 are removed from the C1.

UPDATE JD Set id = replace (ID, ' 1 ', ' 2 ');

The meaning is to change all 1 in the ID column of the JD table to 2.

Translate is just one character to replace the corresponding one character

TRANSLATE (expr, from_string, to_string)

To put it simply, the contents of expr are replaced with characters from the to_string in the from_string.

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 ', ' ') ' from dual         11a21s2   

Brief comparison:

Replace the Replace string level

such as: SELECT REPLACE (' ACCD ', ' CD ', ' EF ') from dual; -AEFD



Translate character-level substitution

such as: Select Translate (' ACDD ', ' CD ', ' EF ') from dual; -->aeff

There is a special use of "#" in translate, which begins with # to denote all characters

The main function of translate is to extract and replace strings, which are sometimes similar to replace. See the example below

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  

Using translate to implement keyword filtering

Sometimes you need to filter some key words, directly using replace, it may be because these keywords are more nested use, statements are not written, but also waste resources. This can actually be achieved using the combination of translate and replace.

For example, "Shenzhen", "Beijing" as the key words, in the display content is to filter out these words do not display:

--First Use TRANSLATE to convert key words uniformly into a special string, such as x         select TRANSLATE (' Shanghai Beijing Tianjin Chongqing Guangzhou Shenzhen Wuhan ', ' Shenzhen Beijing ', ' XXXX ') from dual;           Shanghai xx Tianjin Chongqing Guangzhou xx Wuhan         Select replace (TRANSLATE (' Shanghai Beijing Tianjin Chongqing Guangzhou Shenzhen Wuhan ', ' Shenzhen Beijing ', ' XXXX '), ' X ') from dual;         Shanghai Tianjin Chongqing Guangzhou Wuhan         Select TRANSLATE (' Shanghai Jing Tianjin Chongqing North Guangzhou Shenzhen Wuhan ', ' Shenzhen Beijing ', ' XXXX ') from dual;          Shanghai x Tianjin Chongqing x Guangzhou xx Wuhan   

A brief analysis of substitution functions in Oracle