How POSITION and INDEX Differ - Analytics Database - Teradata Vantage

INDEX and POSITION behave identically except when the session client character set is KanjiEBCDIC, the server character set is KANJI1, and the parent string contains a multibyte character.

This is the only case for which the results of these two functions differ when performed on the same data.

Suppose we create the following table.

   CREATE TABLE iptest (
    column_1 VARCHAR(30) CHARACTER SET Kanji1
    column_2 VARCHAR(30) CHARACTER SET Kanji1);

We then insert the following set of values for the columns.

The client session character set is KanjiEBCDIC5026_0I. Now we perform a query that demonstrates how INDEX and POSITION return different results in this condition.

   SELECT column_1, column_2, INDEX(column_1,column_2)
   FROM iptest;

The result of this query looks like the following:

   column_1     column_2        Index(column_1,column_2)
   -----------  -----------     ------------------------
   MN<AC
>       <C
>                                    6
   MN<AC
>P      <A
>                                    4
   MN<AB
>P      P                                      9
   MN<AB
>P      <B
>                                    6

With the same session characteristics in place, perform the semantically identical query on the table using POSITION instead of INDEX.

   SELECT column_1, column_2, POSITION(column_2 IN column_1)
   FROM iptest;

The result of this query looks like the following:

   column_1     column_2     Position(column_2 in column_1)
   -----------  -----------  ------------------------------
   MN<AC
>       <C
>                                       4
   MN<AC
>P      <A
>                                       3
   MN<AB
>P      P                                         5
   MN<AB
>P      <B
>                                       4