How POSITION and INDEX Differ - 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ā„¢

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.