How POSITION and INDEX Differ

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.

column_1 column_2
MN<AC> <C>
MN<AC>P <A>
MN<AB>P P
MN<AB>P <B>

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
The different results are accounted for by the following differences in how INDEX and POSITION operate in this particular case.
  • INDEX counts Shift-Out and Shift-In characters; POSITION does not.
  • INDEX counts bytes; POSITION counts logical characters. As a result, an A , for example, counts as two bytes (two physical characters) for INDEX, but only one logical character for POSITION.