Usage of substr and instr in Oracle

Collect and organize online data

 

1. substr (string, int A, int B)
Parameter 1: string to be processed
Parameter 2: A truncates the start position of the string (the start position is 0)
Parameter 3: the length of the string intercepted by B (rather than the end position of the string)
For example:
Substr ("abcdefg", 0); // return: abcdefg, intercepting all characters
Substr ("abcdefg", 2); // return: cdefg, intercepting all characters starting from C
Substr ("abcdefg", 1, 3); // return: ABC, intercepting three characters starting from A. Here the values start from 1 and 3 and return a string.
Substr ("abcdefg", 1,100); // return: abcdefg, 100 although the maximum length of the pre-processed string is exceeded, the returned results are not affected. The system returns the maximum number of pre-processed strings.
Substr ("abcdefg", 0,-3); // return: EFG. Note that if the value of-3 is negative, the position of the string is not changed.

2. substr (string, int)
Parameter 1: string to be processed
Parameter 2: A can be interpreted as intercepting a string starting from index A (Note: The starting index is 0) or intercepting a string starting from the character (A + 1.
For example:
Substr ("abcdefg", 0); // return: abcdefg, intercepting all characters
Substr ("abcdefg", 2); // return: cdefg, intercepting all characters starting from C

 

 

 

 

1. instr

 

In Oracle/PLSQL, The instr function returns the position of the string to be truncated in the source string.

 

Syntax: instr (string1, string2 [, start_position [, nth_appearance])

 

String1 source string to be searched in this string.

String2: the string to be searched in string1.

Start_position indicates the position of string1 to start searching. This parameter is optional. If omitted, the default value is 1. The string index starts from 1. If this parameter is positive, it is retrieved from left to right. If this parameter is negative, from right to left, the start index of the string to be searched in the source string is returned.

 

Nth_appearance indicates the number of string2. this parameter is optional. If omitted, the default value is 1. If it is negative, an error is returned.

 

Note:

If string2 is not found in string1, The instr function returns 0.

 

Applicable:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Example:

Select instr ('abc', 'A') from dual; -- returns 1
Select instr ('abc', 'bc') from dual; -- returns 2
Select instr ('abc abc', 'A', 1, 2) from dual; -- return 5
Select instr ('abc', 'bc',-) from dual; -- returns 2
Select instr ('abc', 'D') from dual; -- returns 0