Subfield() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter.
The Subfield() function can be used, for example, to extract first name and surname from a list of records consisting of full names, the component parts of a path name, or for extracting data from comma-separated tables.
If you use the Subfield() function in a LOAD statement with the optional field_no parameter left out, one full record will be generated for each substring. If several fields are loaded using Subfield() the Cartesian products of all combinations are created.
Syntax:
SubField(text, delimiter[, field_no ])
Return data type: string
Arguments:
| Argument | Description |
|---|---|
| text | The original string. This can be a hard-coded text, a variable, a dollar-sign expansion, or another expression. |
| delimiter | A character within the input text that divides the string into component parts. |
| field_no | The optional third argument is an integer that specifies which of the substrings of the parent string text is to be returned. A negative value causes the substring to be extracted from the right-hand side of the string. That is, the string search is from right to left, instead of left to right, if field_no is a positive value. |
Tip note
SubField() can be used instead of using complex combinations of functions such as Len(), Right(), Left(), Mid(), and other string functions.