Contents

1. 1 Definition
2. 2 Item Example with SUBSTRING
3. 3 Item Example with SPLIT_TEXT

Definition

Split text with special characters

Item Example with SUBSTRING

XR splits most fields automatically into the full name, parent, and sub. Where this function is extremely helpful, is where the list information is formatted with three or more levels. For example, Item:Sub-item:Sub-item.

Item = Interior:Door kit
                 
SUBSTRING([Item],0,POSITION(":",[Item])-1)
Result = Interior

SUBSTRING([Item],POSITION(":",[Item])+1)
Result = Door kit

This example would be appropriate for splitting the data separated by ':'
The first example is starting with the Item column and the beginning of the data up to the first : (i.e. the -1 means everything before the ":" )
The second example would display everything after the ":" and if this result also contains a ":" then the formulas could be used to split the newly created column again.

Item Example with SPLIT_TEXT

Depending on the circumstance, SPLIT_TEXT may be more efficient, especially if there are multiple segments. In the following example, the item number has a consistent format that is divided by "-" even though each segment may not be a consistent length. The first segment could designate the product line, the next segment the vendor, and the last segment the vendor's part number. By splitting the data into separate columns, each segment can be used to group the report in different ways.

Item = 1234-56-7890

SPLIT_TEXT([Item],"-",1)
Result = 1234

SPLIT_TEXT([Item],"-",2)
Result = 56

SPLIT_TEXT([Item],"-",3)
Result = 7890

This example would be appropriate for splitting the item based on the "-"
The number at the end of the formula indicates what segment of the data should be pulled out.