oracle Intercepting Character of substr Retrieving Character Position of instr Example Introduction

1: Theory

oracle intercepts characters (substr), retrieves character positions (instr), case when then else end statements use favorites
Common functions: substr and instr
1. SUBSTR (string, start_position, [length]) Find a substring and return a string
Explanation: string meta string
start_position start position (starting from 0)
length optional, number of substrings
For example:

substr("ABCDEFG", 0); // Return to: ABCDEFG Intercept all characters 
substr("ABCDEFG", 2); // Return to: CDEFG , intercepting from C All characters after start 
substr("ABCDEFG", 0, 3); // Return to: ABC , intercepting from A Begin 3 Characters 
substr("ABCDEFG", 0, 100); // Return to: ABCDEFG , 100 Although it exceeds the maximum length of preprocessed strings, it will not affect the returned results, and the system will return according to the maximum number of preprocessed strings. 
substr("ABCDEFG", -3); // Return to: EFG Attention parameters -3 A negative value means that the string arrangement position does not change from the beginning of the tail. 

2. INSTR (string, subString, position, ocurrence) Lookup string position
Explanation: string: Source string
subString: Substring to find
position: Start location of lookup
ocurrence: The first occurrence of a substring in the source string
For example:
In INSTR ('CORPORATE FLOOR', 'OR', 3, 2), the source string is' CORPORATE FLOOR ', the target string is' OR ', the starting position is 3, and the position of the second occurrence is taken; The result returned is 14 '

2: Actual testing

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;

Successful test