SPLIT( ) function
Returns a specified segment from a string.
Syntax
SPLIT(string, separator, segment <,text_qualifier>)
Parameters
| Name | Type | Description |
|---|---|---|
| string | character |
The field, expression, or literal value to extract the segment from. |
| separator | character |
The character or characters that delimit segments. For more information, see How the separator character works. |
| segment | numeric |
The segment to extract. Use a number to specify which segment to extract. For example, to extract the third segment, specify 3. |
| text_qualifier
optional |
character |
The character or characters that indicate the start and end of segments of text. If the separator character occurs inside a paired set of text qualifiers, it is read as text and not as a separator. You must enclose the text qualifier in quotation marks. A single quotation mark text qualifier must be enclosed in double quotation marks, and a double quotation mark text qualifier must be enclosed in single quotation marks. Tip This optional parameter can be useful when working with imported source data that retains separators and text qualifiers. |
Output
Character.
Examples
Basic examples
Comma-delimited segments
Returns "seg1":
SPLIT("seg1,seg2,seg3", ",", 1)
Returns "seg3":
SPLIT("seg1,seg2,seg3", ",", 3)
Returns "" (the third segment is empty):
SPLIT("seg1,seg2,,seg4", ",", 3)
Multi-character and space delimiters
Returns "seg3":
SPLIT("seg1/*seg2/*seg3", "/*", 3)
Returns "Doe":
SPLIT("Jane Doe", " ", 2)
Escaping delimiters with a text qualifier
Returns "Doe, Jane", which includes a comma that is read as text rather than as a separator:
SPLIT('"Doe, Jane","Smith, John"', ",", 1, '"')
Advanced examples
Extracting digits from a credit card number
Use the SPLIT( ) command 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.
ASSIGN seg1 = SPLIT("4150-2222-3333-4444", "-", 1)
ASSIGN seg2 = SPLIT("4150-2222-3333-4444", "-", 2)
ASSIGN seg3 = SPLIT("4150-2222-3333-4444", "-", 3)
ASSIGN seg4 = SPLIT("4150-2222-3333-4444", "-", 4)
ASSIGN ccNum = seg1 + seg2 + seg3 + seg4
The value of ccNum is "4150222233334444".
The example illustrates the SPLIT( ) function, but note that the dashes can be removed more efficiently using the EXCLUDE( ) function.
Remarks
How it works
The SPLIT( ) function breaks character data into segments based on separators such as spaces or commas and returns a specified segment.
When to use SPLIT( )
Use the SPLIT( ) function to extract a particular segment of data from a record or field. The segment must appear in the same position in each record or field.
How the separator character works
The separator character delimits, or indicates, the segments of data in a source string.
In a string with a number of segments, most of the segments appear between two separators. However, the first segment may not have a separator character preceding it, and the last segment may not have a separator character following it.
If the source string does not begin with a separator, the segment preceding the first separator is treated as segment 1.
Returns "seg1":
SPLIT("seg1,seg2,seg3", ",", 1)
If the source string begins with a separator, segment 1 is consider to be null. The segment that follows the separator is treated as segment 2.
Returns "seg1":
SPLIT(",seg1,seg2,seg3", ",", 2)
Case sensitivity
If separator or text_qualifier specify characters that have both an uppercase and a lowercase version, the case used must match the case of the separator or text qualifier in the data.
Related functions
SPLIT( ) and SUBSTR( ) both return a segment of data from a longer source string.
- SPLIT( ) identifies the segment based on a separator character.
- SUBSTR( ) identifies the segment based on a numeric character position.
