Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

SUBSTRING

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

Description

SUBSTRING returns the substring of size size starting at character with zero-based index start.

Substring uses zero-based indexing, i.e., the first character is at start=0. If the requested character range is (partially) outside of the string, only the overlapping part is returned.

Supported input column types: STRING

Output column type: STRING

Syntax
SUBSTRING ( table.column, start, size ) 

The following apply to special values of size and start:

  • size is negative: An error will be returned.

  • size is zero: An empty STRING will be returned for all input, except NULL.

  • start is negative:

    • If the sum of start and size is zero or negative, an empty STRING will be returned for all input, except NULL.

    • If the sum is positive, then start + size characters will be returned, starting from index zero of the input. If the input is NULL, NULL will be returned.

NULL handling

If the value is NULL, the result is NULL as well.

Examples

[1] Return substring of size 1 at position 0 (the first addressable position).

Query

Column1

SUBSTRING ( "Table1"."Column1" , 0 , 1 )

Input

Output

Table1

Column1 : STRING

'???o'

Result

Column1 : STRING

'?'

[2] Return substring of size 1 at position 1 (the second addressable position).

Query

Column1

SUBSTRING ( "Table1"."Column1" , 1 , 1 )

Input

Output

Table1

Column1 : STRING

'???o'

Result

Column1 : STRING

'?'

[3] Return substring of size 2 at position 3 (the fourth addressable position). As the string ends after position 3, the returned string is of size 1.

Query

Column1

SUBSTRING ( "Table1"."Column1" , 3 , 2 )

Input

Output

Table1

Column1 : STRING

'???o'

Result

Column1 : STRING

'o'

See also: