Method: Delimited extract
Function name: de_delimit_sub_string
Parameter list: (CHAR input, CHAR delimiter, INTEGER position)
Return type: CHAR
What the method does: Controlled by one or more delimiter characters, this method extracts a character substring from a source string based on the substring being delimited on its left and right side by one of the delimiter characters.
The method has the following three parameters:
Character source column or expression value.
String of one or more single delimiter characters specified in any order.
Ordinal number (1, 2, 3, etc.) of the position of the delimited substring
For example, if the following expression is specified:
de_delimit_sub_string('203-435-4567 x305','-',2);
where '203-435-4567 x305' is the source string, the delimiter is dash (-), and the ordinal number is 2, the method returns: '435'. This represents all the characters that comprise the 2nd substring that is selected using the specified delimiter character, a dash.
In cases where the input source value is NULL or an empty string or the substring is not found (for example, because the ordinal number is too large), the result of the delimit extract method is NULL.
Note: The delimiter can be more than 1 character. If more than 1 character is used, then the search for a substring match always uses all the delimiters in the list of characters as candidates for a delimiter.
In the following example, two different delimiter characters used to extract the desired substring: de_delimit_sub_string('SS-TT*UU-SS*','*-', 2); yield a result of 'TT'.
Note: The set of one or more delimiter characters ALWAYS includes the following default delimiters in addition to those specified:\r - return character\n - new line character\0 - binary zero character
For example, the method:
de_delimit_sub_string('a\0aa\0aaa','', 2);
returns the value 'aa'.
Also note that the beginning and end of the string will act as implicit delimiters. For example the following 2 methods:
de_delimit_sub_string('.A.AA.AAA.AAAA.', '.', 1);
de_delimit_sub_string('A.AA.AAA.AAAA.', '.', 1);
both return the value 'A'.