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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.