Oracle intercepts character Substr, retrieves character position InStr

Common functions: Substr and InStr
1.SUBSTR (String,start_position,[length]) to substring, return string
Explanation: String meta strings
Start_position start position (starting from 0)
Length optional, number of substrings

substr ("ABCDEFG", 0); --Return: ABCDEFG, intercept all characters substr ("ABCDEFG", 2); --Return: CDEFG, intercept all characters after starting with C substr ("ABCDEFG", 0, 3); --Return: ABC, intercept starting from a 3 characters substr ("ABCDEFG", 0, 100); --Return: abcdefg,100 The maximum number of preprocessed strings is returned, although it exceeds the length of the preprocessed string, but does not affect the return result. substr ("ABCDEFG",-3); --Return: EFG, note that parameter 3, which is negative, indicates that the string is positioned unchanged from the beginning of the tail.

2.INSTR (string,substring,position,ocurrence) Find string position
Explanation: string: source string
SubString: substring to find
Position: Where to find start
Ocurrence: A substring of the first occurrence of a source string

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 taken, and the result is 14 '

Example SUBSTR:

SELECT * FROM emp twhere substr (t.job,3,5) = ' Alyst '

You can get: data for the job ANALYST.

　

Oracle intercepts character Substr, retrieves character position InStr