Oracle character escape operation function

Instr usage is transferred from the http://www.cnblogs.com/Xrinehart/archive/2005/04/21/142791.html.

The format of the instr method is
Instr (source string, target string, start position, matching serial number)

For example, in instr ('upgrade floor ', 'or', 3, 2), the source string is 'upgrade floor', the target string is 'or', And the start position is 3, take the location of 2nd matching items.

The default search order is left to right. When the start position is negative, search from the right.

Therefore, the result of select instr ('upgrade floor ',' or ',-1, 1) "instring" from dual is:

Instring
------
14

 

Translate vs. Replace

Translate (SRC, from_str, to_str): If from_str exists in SRC, the function of translate is equivalent to replace, and replace all from_str in the source character in SRC with to_str. however, if SRC only contains the from_str part, this situation is a bit different.

1. Select translate ('12o12o', '12', '1'), replace ('12o12o12o', '12', '1') from dual;
In this example, translate and replace play the same role.

2. Select translate ('kaxk', 'kx', '12'), translate ('kaxk', 'kk', '12') from dual;

Translate ('kaxksx ', 'kx', '12') Translate ('kaxksx', 'kks ', '12 ')
-----------------------------------------------------------------------
11a21s2 11ax1x

Translate maps 'kx' and '12' by character. It maps all 'K'-> '1' in 'kaxk ', 'X'-> '2' (in replace, from_str is not decomposed, but regarded as a whole ).
If from_str is longer than to_str, for example, from_str: 'kxs ', to_str: '12', then 'K'-> '1', 'X'-> '2 ', 'S '-> ''; the last one is empty.

In the second replacement, from_str has a duplicate value 'k', so the following method is used in the toing with to_str:
'K'-> '1'. Because 'K' has been mapped, the second character does not match, but the character ing pointers of from_str and to_str both move to the back one, eventually, the second digit of to_str is ignored. the third digit of from_str can only match ''empty escape.