This transform might be automatically applied as one of the first steps of your recipe. See Initial Parsing Steps.

When the split transform is applied, the source column is dropped. 

• Before applying this transform, you can create a copy of the source column using the derive transform. See Derive Transform. 
• To retain the source column, you can use the extract transform and pattern-based matching. See Extract Transform.

Output: Splits the source MyValues column into four separate columns. Values in the columns are determined based on the comma (,) delimiter. If a row only has two commas in it, then the final generated column is null.

The split transform supports the following general methods for specifying the delimiters by which to split the column. Depending on your use of the transform, different sets of parameters apply.

Shared parameters:

The following parameters are shared between the operating modes:

Single-pattern delimiter parameters:

Multi-pattern delimiter parameters:

col

Identifies the column to which to apply the transform. You can specify only one column.

Output: Splits the MyCol column into two separate columns whose values are to the left and right of the MyString value in each cell.

• If a delimiter value is not detected, the cell value appears in the first of the new columns.
• When the limit parameter is not specified, the default value of 1 is applied.

ignoreCase

Output: Splits the MyCol column on case-insensitive versions of the on parameter value, if they appear in cell values: My String, my string, My string, etc.

limit

Output: Splits the MyCol column on each instance of the letter z, generating 4 new columns. If there are fewer than 3 instances of z in a cell, the corresponding columns after the split are blank.

• Defines the number of columns that can be created by the split transform. 
• If not specified, exactly one column is created. See Pattern Clause Position Matching.

after

• If this parameter is the only pattern describer: 
  • The column is split into two. The first column contains the part of the source column before the after matching value. The second column is blank.

  • If the value appears more than once, no additional splitting is made, since there is no other pattern parameter.

    Info
    NOTE: For after, before, from, and to, matching occurs only one time at most. Additional instances of the parameter's value in the cell do not cause another column split. For more predictable results, you should specify another pattern parameter.

• If the after value does not appear in the column, the original column value is written to the first split column.
• This parameter is typically used with another to describe a field delimiting pattern. See below.

Output: Splits values in MyCol based on value between the two characters. The first column contains the part of the MyCol that appears before the backslash (\), and the second column contains the part of MyCol that appears after the pipe character (|). The content between the delimiting characters is dropped.

• The after and from parameters are very similar. from includes the matching value as part of the delimiter string.
• after can be used with either to or before. See  Pattern Clause Position Matching.

at

Output: Splits the MyCol column on the value that begins at the second character in the column and extends to the sixth character of the column. Contents before the value are in the first column, and contents after the value are in the second column.

The at parameter cannot be combined with any of the following:  on,  after, before, from, to, and quote. See  Pattern Clause Position Matching.

before

• If this parameter is the only pattern describer: 
  • The column is split into two. The first column is blank. The second column contains the part of the source column after the before matching value. 

  • If the value appears more than once, no additional splitting is made, since there is no other pattern parameter.

    Info
    NOTE: For after, before, from, and to, matching occurs only one time at most. Additional instances of the parameter's value in the cell do not cause another column split. For more predictable results, you should specify another pattern parameter.

• If the before value does not appear in the column, the original column value is written to the first split column.
• This parameter is typically used with another to describe a field delimiting pattern. See below.

Output: Splits contents of MyCol into two columns. The first column contains the values that appear before the Go: string, and the second column contains the values after the backslash. 

• The before and to parameters are very similar.  to includes the matching value as part of the delimiter string.
• before can be used with either from or after. See  Pattern Clause Position Matching .

from

• If this parameter is the only pattern describer: 
  • The column is split into two. The first column contains the part of the source column before the from matching value. The second column is blank.

  • If the value appears more than once, no additional splitting is made, since there is no other pattern parameter.

    Info
    NOTE: For after, before, from, and to, matching occurs only one time at most. Additional instances of the parameter's value in the cell do not cause another column split. For more predictable results, you should specify another pattern parameter.

• If the from value does not appear in the column, the output value is original column value.
• This parameter is typically used with another to describe a field delimiting pattern. See below.

Output: Splits contents of MyCol from go:, including go: to stop:, including stop:. Contents before the string appear in the first column, contents after the string appear in the second one. 

• The after and from parameters are very similar.  from includes the matching value as part of the delimiter string.
• from can be used with either to or before. See  Pattern Clause Position Matching .

on

If the value does not appear in the source column, the original value is written to the first column of the split columns.

Output: Column into two columns. The first column contains values in the column appearing before ###ERROR, and the second column contains the values appearing after this string.

to

• If this parameter is the only pattern describer: 
  • The column is split into two. The first column is blank. The second column contains the part of the source column after the to matching value. 

  • If the value appears more than once, no additional splitting is made, since there is no other pattern parameter.

    Info
    NOTE: For after, before, from, and to, matching occurs only one time at most. Additional instances of the parameter's value in the cell do not cause another column split. For more predictable results, you should specify another pattern parameter.

• If the to value does not appear in the column, the original column value is written to the first split column.
• This parameter is typically used with another to describe a field delimiting pattern. See below.

Output: Splits MyCol column all contents that appear before note: in the first column and all contents that appear after the first space after note: in the second column. 

• The before and to parameters are very similar.  to includes the matching value as part of the delimiter string.
• to can be used with either from or after. See Pattern Clause Position Matching.

quote

Output: Splits the MyLog column, on the pipe character (|), while ignoring any pipe characters that are found between double-quote characters in the column. Based on the value in the limit parameter, the transform is limited to creating a maximum of 10 splits. 

• Parameter value is the quoted object.
• The quote value can appear anywhere in the column value. It is not limited by the constraints of any other parameters.

delimiters

The delimiters parameter specifies a comma-separated list of string literals or patterns to identify the delimiters to use to split the data. Values can be string literals, regular expressions, or 

• The sequence of values defines the order in which the delimiters are applied.
• Values do not need to be the same.

positions

The positions parameter specifies a comma-separated list of integers that identify zero-based character index values at which to split the column. Values must be Integers.

• column1 = characters 0-20 from the source column, 
• column2 = characters 21-55
• column3 = characters 56-80
• column4 = characters 80 to the end of the cell value

every

The every parameter can be used to specify fixed-width splitting of the source column. This Integer value defines the number of characters in each column of the split output.

If needed, you can use the every parameter with the limit parameter to define the maximum number of output columns:

Pattern Groups

When you build or edit a split transform step in the Transform Builder, you can select one of the following pattern groups to apply to your transform. A pattern group is a set of related patterns that define a method of matching in a cell's data. Some pattern groups apply to multiple transforms, and some apply to the split transform only. For more information, see Transform Builder.

Example - Split with single pattern delimiters

Source:

Transform:

ColA: You can use the following transform to split on the variations of My String: In this case, the ignoreCase parameter ensures that all variations on capitalization are matched:

ColB: For this column, the letter x is the split marker, and the data is consistently formatted with three instances per row:

ColC: In this column, the double-letter marker varies between the rows. However, it is consistently in the same location in each row:

Results:

When the above transforms are added, the source columns are dropped, leaving the following columns:

Example - Split with quoted values

Example - Splitting with different delimiter types