An example of the character location (instr) retrieved by the oracle character truncation (substr) is as follows: substrinstr

An example of the character location (instr) retrieved by the oracle character truncation (substr) is as follows: substrinstr

I. Theory

Oracle truncation character (substr), search character location (instr) case when then else end statement use favorites
Common functions: substr and instr
1. SUBSTR (string, start_position, [length]) returns a substring.
Explanation: string metacharacters
Start_position start position (starting from 0)
Length option, number of substrings
For example:

Substr ("ABCDEFG", 0); // return: ABCDEFG, intercepting all characters substr ("ABCDEFG", 2); // return: CDEFG, truncates all substr ("ABCDEFG", 0, 3) after starting from C; // returns ABC, truncates three substr ("ABCDEFG", 0,100) characters starting from ); // return: Although ABCDEFG and 100 exceed the maximum length of the pre-processed string, the returned results are not affected. The system returns the maximum number of pre-processed strings. Substr ("ABCDEFG",-3); // return: EFG. Note that if the value of-3 is negative, the value starts from the end and the position of the string remains unchanged.

2. INSTR (string, subString, position, ocurrence) to find the string position
Explanation: string: Source string
SubString: The subString to be searched.
Position: the start position of the query.
Ocurrence: the nth occurrence of the substring in the source string
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, returns 14'

Ii. 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

How Does oracle intercept a string after a specified character, or delete a string before a specified character?

Take the character after the last \
Select substr ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ', 1 + (select instr ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ','\', 1, (select length ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ')-length (replace ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ', '\', '') from dual)
, Length ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ')-(select instr ('d: \ AssetsManagement \ resource \ ehcache \ test.txt', '\', 1, (select length ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ')-length (replace ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ','\', '') from dual)
) From dual

If D: \ AssetsManagement \ resource \ ehcache \ is fixed, replace it directly.
Select replace ('d: \ AssetsManagement \ resource \ ehcache \ test.txt ', 'd: \ AssetsManagement \ resource \ ehcache \', '') from dual

What are the characters before spaces in the oracle truncation field? PL/SQL implementation

You can use several Oracle system functions. Statement: select substr ('ahs1234 100', 0, INSTR ('ahs1234 100', '')-1) str FROM dual; Result: AHS1234
The instr function is used to find the position of a space in a string. The SUBSTR function is used to intercept a string from the start of a string to the first digit of the space position (for example, the position of a space is 8, intercept 8-1 = 7 characters)