POSITION FUNCTION IN TERADATA

Share via:
POSITION FUNCTION IN TERADATA

Dear Readers,

In this article, we will learn about POSITION FUNCTION IN TERADATA

Purpose 

Returns the position in string_expression_2 where string_expression_1 starts.

Syntax 

string_expression_1 :

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

string_expression_2 :

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

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.

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.

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.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment