Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

D toc

Excerpt

Matches some or all of a string, based on the user-defined starting and ending index values within the string.

  • Input can be a column reference or a string literal value.
  • Since the SUBSTRING function matches based on fixed numeric values, changes to the length or structure of a data field can cause your recipe to fail to properly execute.
  • The SUBSTRING function requires numerical values for the starting and ending values. If you need to match strings using patterns, you should use the extract transform instead. See Extract Transform.

...

D code

derive type:single value:SUBSTRING(ZipCode,1,3)

D code

derive type:single value:SUBSTRING(ZipCode,3,5)

 

Since the string can be five or ten characters in length, you need to use the SUBSTRING function in the second transform, too. If the data is limited to five-digit zip codes, you could use the RIGHT function.

Results:

LastNameZipCodesubstring_ZipCodesubstring_ZipCode2
Able941014101
Baker23502-11223502
Charlie368456845

...