The SPLIT_PART function is a helpful way to extract parts of strings that have delimited data embedded. For example, suppose you have a string in a variable "Full Address" with data like the following:
'123 Main Street, Apt 1'
The objective is to create two variables from this string, one with the "Street Address", and another with the "Address 2" component.
One key aspect of being able to perform this kind of operation consistently and correctly is knowledge of how the initial string is structured, and that its structure remains consistent across your dataset. With that in place, you can construct code knowing what range of inputs to expect.
In this example, we will assume that the string always has the following structure: [Street Address] - followed by a comma - followed by a space - followed by [Address 2].
Here is the code we can use to create the two new fields.
|
New Field |
Code |
| Street Address | SPLIT_PART([Full Address], ',', 1) |
| Address 2 | TRIM(SPLIT_PART([Full Address], ',', 2)) |
Explanation:
To use the SPLIT_PART function, you specify three parameters:
- The string variable (or literal string if you want) to work on
- The string (e.g., comma) that separates the components you are looking to extract
- The number of the component to extract. For example, 2 retrieves the part of the string after the first comma and before the 2nd comma if there was one
The TRIM function is also used here because we know that the 2nd piece will always start with a space. Assuming we do not really want the space character to start our Address 2 field, TRIM will remove it.
Finally, it is a good idea to explicitly provide LityxIQ the maximum string length you expect to result from performing the splitting operation. This helps LityxIQ allocate the appropriate length to the resulting string. See https://support.lityxiq.com/959975-Using-the-String-Length-Setting-When-Creating-a-New-Field for more information.