SPLIT( ) function

Returns a string containing the text between two separators in the specified string.

Syntax

SPLIT(string, separator, segment <,text_qualifier>)

Parameters

string

Character. The value to extract the text string from.

separator

Character. The character or character string that identifies the start of the segment to extract.

segment

Numeric. The number of the segment to extract.

text_qualifier

Optional. Character. The character or character string to use as a text qualifier. You must enclose the text qualifier in quotation marks. If the quotation mark is used as the text qualifier, the character string and the text qualifier quotation marks must differ from each other. For example, if the file contains single-quotation-mark text qualifiers, you must enclose the character string in double quotation marks.

Output

Character.

Remarks

The SPLIT( ) function breaks character data into segments based on separators such as spaces or commas and returns a specified segment. The segment is returned as a fixed length string which is equal to the length of the input string. If the specified segment does not exist in string, the output is a string of spaces. You can use this function to extract data from a record or field.

The characters between two separators make up a segment. When the source string begins with a separator, the segment that follows the separator is treated as segment two. The comparison of the strings is case-sensitive.

A variety of characters, such as commas, semicolons, tabs, pipes (|), or spaces can separate fields. The same range of characters can also represent text qualifiers, which distinguish punctuation that serves as text from punctuation that serves as field separators. For example, in a comma-delimited file, the quotation marks around the field “Last name, First name” indicate that the comma represents text rather than a field separator.

The optional text_qualifier parameter is designed for use with data imported from delimited files. The parameter is used to indicate instances of separator that you do not want SPLIT( ) to treat as a separator.

Examples

Example Return value

SPLIT("seg1,seg2,seg3", ",", 2)

“seg2”

SPLIT("seg1,seg2,seg3", ",", 3)

“seg3”

SPLIT("seg1/*seg2/*seg3", "/*", 3)

“seg3”

SPLIT("Jane Doe", " ", 2)

“Doe”

SPLIT("seg1,seg2,,seg4", ",", 3)

“”

The third segment is empty.

Extracting digits from a credit card number

The following example shows how the SPLIT( ) command can be used to remove dashes from a credit card number. Variables are used to capture each segment of the credit card number, and then the segments are concatenated together in an additional variable.

seg1 = SPLIT("4150-2222-3333-4444", "-", 1)
seg2 = SPLIT("4150-2222-3333-4444", "-", 2)
seg3 = SPLIT("4150-2222-3333-4444", "-", 3)
seg4 = SPLIT("4150-2222-3333-4444", "-", 4)
ccNum = seg1 + seg2 + seg3 + seg4

The value of ccNum is “451022223333444”.

The above example illustrates the SPLIT( ) function, but note that the dashes can be removed more efficiently using the INCLUDE( ) function.

Related reference
CALCULATE command


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback