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:
If one string expression is of type BYTE, then both expressions must be of type BYTE.
Numeric string expressions are converted implicitly to CHARACTER type.
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.