POSITION FUNCTION IN TERADATA
In this article, we will learn about POSITION FUNCTION IN TERADATA
Returns the position in string_expression_2 where string_expression_1 starts.
a substring to be searched for its position within the full string.
a full string to be searched
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 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:
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause.
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)
POSITION returns a value according to the following rules.
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.
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.