The following table shows examples of simple INDEX expressions and their results.
| Expression | Result |
|---|---|
| INDEX('catalog','log') | 5 |
| INDEX('catalog','dog') | 0 |
| INDEX('41424344'XB,'43'XB) | 3 |
The following examples show how INDEX(string_1, string_2) operates when the server character set for string_1 and the server character set for string_2 differ. In these cases, both arguments are converted to UNICODE (if needed) and the characters are matched logically.
| IF string_1 is … | AND string_2 is … | THEN the result is … | ||
|---|---|---|---|---|
| Character Set | Data | Character Set | Data | |
| UNICODE | 92  abc |
LATIN | abc | 4 |
| UNICODE | abc | UNICODE | c | 3 |
| KANJISJIS | 92 04 |
UNICODE | 0 | 4 |
The following examples show how INDEX(string_1, string_2) operates when the server character set for both arguments is KANJI1 and the client character set is KanjiEBCDIC.
Note that for KanjiEBCDIC, results are returned in terms of physical units, making INDEX DB2-compliant in that environment.
| IF string_1 contains … | AND string_2 contains … | THEN the result is … |
|---|---|---|
| MN<AB > | <B > | 6 |
| MN<AB > | <A > | 4 |
| MN<AB >P | P | 9 |
| MX N<AB >P | <B > | 7 |
The following examples show how INDEX(string_1, string_2) operates when the server character set for both arguments is KANJI1 and the client character set is KanjiEUC.
| IF string_1 contains … | AND string_2 contains … | THEN the result is … |
|---|---|---|
| a b ss3 A | ss3 A | 3 |
| a b ss2 B | ss2 B | 3 |
| CS1_DATA | A | 6 |
| a b ss2 D ss3 E ss2 F | ss2 F | 5 |
| a b C ss2 D ss3 E ss2 F | ss2 F | 6 |
| CS1_D mATA | A | 7 |
The following examples show how INDEX(string_1, string_2) operates when the server character set for both arguments is KANJI1 and the client character set is KanjiShift-JIS.
| IF string_1 contains … | AND string_2 contains … | THEN the result is … |
|---|---|---|
| mnABC X | B | 4 |
| mnABC X | X | 6 |
In this example, INDEX is applied to ' ' (the SPACE character) in the value strings in the Name column of the Employee table.
SELECT name FROM employee WHERE INDEX(name, ' ') > 6 ;
INDEX examines the Name field and returns all names where a space appears in a character position beyond the sixth (character position seven or higher).
The following example displays a list of projects in which the word Batch appears in the project description, and lists the starting position of the word.
SELECT proj_id, INDEX(description, 'Batch') FROM project WHERE INDEX(description, 'Batch') > 0 ;
The system returns the following report.
proj_id Index (description, 'Batch') ------------- ---------------------------- OE2-0003 5 AP2-0003 13 OE1-0003 5 AP1-0003 13 AR1-0003 10 AR2-0003 10
A somewhat more complex construction employing concatenation, SUBSTRING, and INDEX might be more instructive. Suppose the employee table contains the following values.
empno name ---------- ----------- 10021 Smith T 10007 Aguilar J 10018 Russell S 10011 Chin M 10019 Newman P
You can transpose the form of the names from the name column selected from the employee table and change the punctuation in the report using the following query:
SELECT empno, SUBSTRING(name FROM INDEX(name,' ')+1 FOR 1)|| '. '|| SUBSTRING(name FROM 1 FOR INDEX(name, ' ')-1) (TITLE 'Emp Name') FROM employee ;
The system returns the following report.
empno Emp Name ---------- -------------- 10021 T. Smith 10007 J. Aguilar 10018 S. Russell 10011 M. Chin 10019 P. Newman