SplitPart

The SplitPart function splits a string into multiple parts at the position of a specified delimiter in the string, then returns the nth part, where n is the position.

SplitPart allows you to extract portions of a string defined by a repeating pattern, such as words between spaces, or values in a JSON string.

Syntax

SplitPart(string, delimiter, position)

string (required) - The source string to be split.

delimiter (required) - The substring to split the string on. The delimiter is excluded when splitting the string into parts.

position (required) - The index of the part to return. When searching the string for the delimiter, the string is broken into parts each time the delimiter is found. These parts are numbered from left to right, starting with 1. Negative numbers for the index will start counting the index from the right.

Considerations

• Functionality in edge cases varies by data platform:
  • When using an empty string "" as the delimiter and the position 1, some platforms return the original string, while others return an empty string "".
    • Example: SplitPart("abc", "", 1) can return "abc" or "".
  • When the delimiter is not found in the string, some platforms return null, while others return an empty string "".
    • Example: SplitPart("a-b-c", "_", 2) can return null or "".
  • When the position is 0 or out-of-bounds (the absolute value of the position is greater than the number of parts), some platforms return null, while others return an empty string "".
    • Example: SplitPart("a-b-c", "-", 4) can return null or "".
• Cached values can result in unexpected outputs, particularly after implementing a formula that contains one of the limitations described above. If you see unexpected results, click Refresh data to confirm they are not caused by caching.

Example

SplitPart("{index1, index2, index3, index4}", ", ", 3)

Splits the string "{index1, index2, index3, index4}" into four parts at each appearance of the delimiter ", ", then returns the third part, "index3".

SplitPart("quick brown fox", " ", 1)

Splits the string "quick brown fox" into three parts at each appearance of the delimiter " ", then returns the first part, "quick".

SplitPart("quick brown fox", " ", -1)

Splits the string "quick brown fox" into three parts at each appearance of the delimiter " ", then returns the last part, "fox".

SplitPart(SplitPart(Text([CustomerJSON]), ",", 3), ":", 2)

Parses the JSON string in the [CustomerJSON] column to extract the third key-value pair, CUST_ADDRESS, then parses the key-value pair to extract the value of the CUST_ADDRESS key.