SUBSTR

SUBSTR(string, position length) extracts a substring from a string.

Syntax and Parameters

SUBSTR(string, position, length)

• String - (required): The original string you want to extract from.
• Position - (required).
  • Positive number. For example 1, 2, 3. Starts from left side
  • Negative number. For example -1, -2, -3. Starts from right side.
  • Cannot be 0.
  • Position counting starts at 1, not 0.
• Length - (optional):
  • If specified: Returns that many characters.
  • If omitted: Returns all remaining characters from the position.

Using Positive Positions

Positive positions count from the left side.

"TRANSFORM" as an example:

SUBSTR("TRANSFORM", 1) -- Returns "TRANSFORM" (full string)
SUBSTR("TRANSFORM", 1, 4) -- Returns "TRAN" (first 4 letters)
SUBSTR("TRANSFORM", 2) -- Returns "RANSFORM" (everything after T)

When no length is specified, SUBSTR continues to the end of the string. For example,SUBSTR("TRANSFORM", 1):

T R A N S F O R M
1 2 3 4 5 6 7 8 9  (positions)
↑
Starts here and continues to end

Using Negative Positions

Negative positions count from the right side.

"TRANSFORM" as an example:

SUBSTR("TRANSFORM", -1) -- Returns "M" (last letter)
SUBSTR("TRANSFORM", -2) -- Returns "RM" (last 2 letters)
SUBSTR("TRANSFORM", -4, 2) -- Returns "FO" (2 letters starting at F)

Position counting for "TRANSFORM" from right:

Position from right:  M(-1)
                     R(-2)
                     O(-3)
                     F(-4)
                     S(-5)
                     N(-6)
                     A(-7)
                     R(-8)
                     T(-9)

Using SUBSTR in Coalesce

When using SUBSTR in transforms and bulk editing, you can use Helper Tokens:

SUBSTR({{SRC}}, 4)

The {{SRC}} token automatically gets replaced with the fully qualified source Node and column name. For example:

• If source column is "CUSTOMER"."FULL_NAME" it becomes SUBSTR("CUSTOMER"."FULL_NAME", 4).
• If source column is "ORDERS"."ORDER_ID" it becomes SUBSTR("ORDERS"."ORDER_ID", 4).