Retrieving substrings in DataStage®

You can retrieve substrings from data by using several different methods.

You can build transformation expressions that retrieve substrings by using the Field, Left, or Right string functions.

Field function

The Field function returns one or more substrings that are located between specified delimiters in a string. The argument occurrence specifies which occurrence of the delimiter is to be used as a terminator. The argument number optionally specifies how many substrings to return.
  • Input: string (string), delimiter (string), occurrence (int32), [number (int32)]
  • Output: result (string)
  • Examples:
    • If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the string " chocolate ice cream".
      Field(mylink.mystring1, ", ", 2)
    • If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the string " chocolate ice cream, chocolate bars".
      Field(mylink.mystring1, ", ", 2, 2)

Left function

The Left function returns the leftmost n characters of a string.
  • Input: string (string) number (int32)
  • Output: result (string)
  • Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the string "chocolate".
    Left(mylink.mystring1,9)

Right function

The Right function returns the rightmost n characters of a string.
  • Input: string (string) number (int32)
  • Output: result (string)
  • Examples. If mylink.mystring1 contains the string "chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers", then the following function returns the string "dippers". 
    Right(mylink.mystring1, 7)