POSITION macro

The POSITION macro is available only in HCL® Campaign.

Syntax

POSITION(colName, pattern [, start [, occurrence]])

Parameters

colName

The value of a column (must be string type).

pattern

The pattern, or string, for which you are searching.

start

The byte with which to begin the search.

occurrence

Specify a value for n, where you are searching for the n th occurrence of the pattern to return.

Description

POSITION returns the starting byte position of a pattern, or string, within the value of a column (colName) which must be string type. If start is specified, it begins to search from there. Occurrence is the nth occurrence of pattern to return.

Note: The search is not case sensitive.

Examples

In the example below, we are searching for the pattern or string, 'A ', within the value of the column, dbo_BaseInfo.BranchCd, and assigning the returned value to a derived filed POStest.

The following example shows a few rows from the table with the values from dbo_BaseInfo.BranchCd and POStest shown side-by-side.



A more complex example:

STRING_SEG(POSITION(CellCode,"X",1,2)+1,

STRING_LENGTH(CellCode),CellCode) = "AAA"

This returns rows where the values of CellCode have "AAA " at the end following the second occurrence of "X ".