Oracle's SUBSTR and InStr functions Simple usage _oracle

Oracle's SUBSTR function simple usage

SUBSTR (string, intercept start position, intercept length)//return the intercepted word

substr (' Hello World ', 0, 1)//return result is ' H ' * to intercept a string with a length of 1 starting from the first character of the string
substr (' Hello World ', 1, 1)//Returns the result is ' H ' *0 and 1 are all representations of the start position of the intercept as the first character
substr (' Hello world ', 2,4)//return result to ' Ello '
substr (' Hello world ', -3,3)//Return result is ' rld ' * negative number (-i) indicates the beginning of the intercept is the right end of the string to the left of the first character

Test:

Select substr (' Hello world ', -3,3) value from dual;

Attached: Simple usage of substring (INDEX1,INDEX2) in Java

function: To intercept a string with a length of index2-index1 starting from a string index (subscript) for a index1 character.

String str= "Hello World";
System.out.println (str.substring (0,5));

Print Result: Hello

Usage of instr in Oracle:

The format of the InStr method is

INSTR (The source string, the string to find, starting with the first few characters, to find the ordinal number of matches)
Returns the location found, or 0 if it is not found.

For example: INSTR (' CORPORATE FLOOR ', ' or ', 3, 2), the source string is ' CORPORATE FLOOR ', look for ' or ' in the string, look for "or" from the third character position, and the position of the 2nd match after the third word.

The default lookup order is left to right. When the starting position is a negative number, start looking from the right.

So the result of the Select INSTR (' CORPORATE FLOOR ', ' or ',-1, 1) "AAA" from dual is

Instring

——————

14