15.00 - POSITION - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

POSITION

Purpose  

Returns the position in string_expression_2 where string_expression_1 starts.

Syntax  

where:

 

Syntax element …

Specifies …

string_expression_1

a substring to be searched for its position within the full string.

string_expression_2

a full string to be searched.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Use POSITION instead of INDEX for ANSI SQL:2011 conformance. POSITION and INDEX behave identically except when the client character set is KanjiEBCDIC and the server character for an argument is KANJI1 and contains multibyte characters.

Use POSITION in place of MINDEX. (MINDEX no longer appears in this book because its use is deprecated and it will not be supported after support for KANJI1 is dropped.)

Argument Types and Rules

POSITION operates on the following types of arguments:

  • Character, except for CLOB
  • Byte, except for BLOB
  • If one string expression is of type BYTE, then both expressions must be of type BYTE.

  • Numeric
  • Numeric string expressions are converted implicitly to CHARACTER type.

  • UDTs that have implicit casts that cast between the UDT and any of the following predefined types:
  • Numeric
  • Character
  • DATE
  • Byte
  • To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

    Implicit type conversion of UDTs for system operators and functions, including POSITION, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

    For more information on implicit type conversion, see Chapter 13: “Data Type Conversions.”

    Result Type and Attributes

    Here are the default result type and attributes for POSITION(arg1 IN arg2):

     

    Data Type

    Heading

    INTEGER

    Position(arg1 in arg2)

    Expected Values

    POSITION returns a value according to the following rules.

     

    IF …

    THEN the result is …

    either argument is null

    null.

    string_expression_1 has length zero

    one.

    string_expression_1 is a substring within string_expression_2

    the position in string_expression_2 where string_expression_1 starts.

    none of the preceding is true

    zero.

    If the arguments are character types, then regardless of the server character set, the value for POSITION represents the position of a logical character, not a byte position.

    How POSITION and INDEX Differ

    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.