INDEX Function Examples | Teradata Vantage - Example: Using Simple INDEX Expressions - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

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