Page tree

 

Support | BlogContact Us | 844.332.2821

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

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 s
snippetBasic

Column reference example:

D code

derive value:SUBSTRING(MyName,0,5)

Output: The first five letters of the MyName column value is written to the new column. 

String literal example:

D code

derive value:SUBSTRING('Hello, World',0,5)

Output: The string Hello is written to the new column.

D s
snippetSyntax

D code

derive value:SUBSTRING(column_string,start_index,end_index)

ArgumentRequired?Data TypeDescription
column_stringYstringName of the column or string literal to be applied to the function
start_indexYinteger (non-negative)Index value for the start character from the source column or value
end_indexYinteger (non-negative)Index value for the end character from the source column or value

D s lang notes

column_string

Name of the column or string constant to be searched.

  • Missing string or column values generate missing string results.
  • String constants must be quoted ('Hello, World').
  • Multiple columns and wildcards are not supported.

D s
snippetusage

Data TypeRequired?Example Value
StringYesMyName

start_index

Index value of the character in the string to begin the substring match.

  • The index of the first character of the string is 0.
  • Value must be less than end_index.
  • If this value is greater than the length of the string, a missing value is returned.

D s
snippetusage

Data TypeRequired?Example Value
Integer (non-negative)Yes0

end_index

Index value of the character in the string that is one after the end the substring match.

  • Value must be greater than start_index.
  • If this value is greater than the length of the string, the end of the string is the end of match. If you know the maximum length of your data, you can use that value here.

D s
snippetusage

Data TypeRequired?Example Value
Integer (non-negative)Yes5


D s
snippetExamples

Example - Sectional Information in Zipcodes

Source:

A US zip code contains five digits with an optional Zip+4 extension consisting of four digits. Valid zip code values can be a mixture of these formats.

Within zip code values, each digit has significance:

  • Digit 1: Zip code section
  • Digits 2-3: Region within section
  • Digits 4-5: area or town within region
  • Digits 6-9: Optional Zip+4 identifier within area or town

Here is some example data: 

LastNameZipCode
Able94101
Baker23502-1122
Charlie36845

Transform:

You are interested in the region and area or town identifiers within a zip code region. You can use the following transforms applied to the ZipCode column to extract this information:

D code

derive value:SUBSTRING(ZipCode,1,3)

D code

derive 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

D s also
labelstring