INDEX(a,b,p,s) is an arithmetic function that determines the position of the substring specified in b within the character string a. a and b are string specifications. Only expressions that have an alphanumeric value are permitted as string specifications.
The parameter p (an expression) is optional. If p is specified (p>=1), it defines the start position for the search for the substring b. If p is not specified, the search is started at position 1.
The parameter s (an expression) is optional. If s is specified, it determines the number of searches for the substring b. If s is not specified, the search is carried out for the first occurrence.
|
Result of the INDEX(a,b,p,s) function |
a, b character strings and b not less than s times substring of a |
0 |
a character string and b empty character string |
p |
a,b,p or s is NULL value |
NULL value |
p or s is special NULL value |
Error message |
SELECT name, INDEX(name,'er') position_er FROM customer
The position of the character string 'er' is to be determined in all customer surnames.
NAME |
POSITION_ER |
Porter |
5 |
Brown |
0 |
DATASOFT |
0 |
Brian |
0 |
Griffith |
0 |
Randolph |
0 |
Smith |
0 |
Jackson |
0 |
Doe |
0 |
Howe |
0 |
Miller |
5 |
Baker |
4 |
Peters |
4 |
TOOLware |
0 |
Jenkins |
0 |