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

...

  • Input 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

SUBSTRING
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:
substring('Hello, World',0,5)

...

substring('Hello, World',0,5)

Output: Returns the string: Hello.

D s
snippetSyntax

SUBSTRING
d-codelang-syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:
substring(string_val,start_index,end_index)

substring(string_val,start_index,end_index)


ArgumentRequired?Data TypeDescription
string_valYstringString 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

...

LastNameZipCode
Able94101
Baker23502-1122
Charlie36845

TransformTransformation:

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

d-

...

trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:

...

substring(ZipCode,1,3)

...

p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuesubstring(ZipCode,1,3)
SearchTermNew formula

D trans
RawWrangletrue
Typestep
WrangleTextderive type:single value:

...

substring(ZipCode,3,5)
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuesubstring(ZipCode,3,5)
SearchTermNew formula

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

...