Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 must be 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 type:single 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 type:single value:SUBSTRING('Hello, World',0,5)

...

D code

derive type:single value:SUBSTRING(columnstring_stringval,start_index,end_index)


ArgumentRequired?Data TypeDescription
columnstring_stringvalYstringName of the column or string 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

...

string_

...

val

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
StringYes
MyName
'This is my string.'

start_index

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

...