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

Source: Internet
Author: User

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

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.