Oracle Intercept character (SUBSTR) retrieving character position (instr) Sample Introduction _oracle

One: Theory

Oracle Intercept character (SUBSTR), retrieving character position (instr) case when then else End statement uses favorites
Common functions: Substr and InStr
1.SUBSTR (String,start_position,[length]) to substring, return string
Explanation: String Meta string
Start_position starting position (starting from 0)
Length optional, number of substrings
For example:

substr ("ABCDEFG", 0); Back: ABCDEFG, intercept all characters
substr ("ABCDEFG", 2);//return: CDEFG, intercept all characters after starting from C
substr ("ABCDEFG", 0, 3);//back: ABC, Interception begins with a 3 character
substr ("ABCDEFG", 0, 100);//return: abcdefg,100 Though the maximum length of the preprocessed string is exceeded, the result is not affected, and the system is returned at the highest number of preprocessing strings.
substr ("ABCDEFG",-3);//return: EFG, note that parameter-3, when negative, indicates that the string arrangement position is unchanged from the tail start.

2.INSTR (string,substring,position,ocurrence) Find string position
Interpretation: string: Source string
SubString: substring to find
Position: Start position of Lookup
Ocurrence: The first occurrence of substrings in the source string
For example:
INSTR (' CORPORATE FLOOR ', ' or ', 3, 2), the source string is ' CORPORATE FLOOR ', the target string is ' OR ', the starting position is 3, the position of the 2nd match is obtained; the result is 14 '

Second: the actual test

Select substr (' or:com.lcs.wc.placeholder.placeholder:860825 ', INSTR (' OR:com.lcs.wc.placeholder.Placeholder : 860825 ', ': ', 1, 2) +1,length (' or:com.lcs.wc.placeholder.placeholder:860825 ')
, INSTR (' OR: com.lcs.wc.placeholder.placeholder:860825 ', ': ', 1, 2,
length (' or:com.lcs.wc.placeholder.placeholder:860825 ' ) from dual;

Test successful