Text Formula Functions
Here are the formula functions that you can use to manipulate text data:
CHR(n)
Returns the character having the binary equivalent to a number operand n in the ASCII character set.
GREATEST(expr, expr [,expr]....)
Compares the values of all the text string operands. It returns the value of the last string in alphabetic order.
INITCAP(expr)
Returns the expression expr with the first letter of each word in uppercase. Delimits the words
or characters that aren't alphanumeric by a white space.
INSTR(expr1, expr2 [,n [,m]])
Searches expr1 beginning
with its nth character for the mth occurrence of expr2 and returns the character position in expr1 for the first character of this occurrence. If n is negative, INSTR counts
and searches backward from the end of expr1. The value of m must be positive.
The default values of both n and m are 1, meaning INSTR begins
searching at the first character of expr1 for the first occurrence of expr2.
The return value is relative to the beginning of expr1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (expr1 doesn't appear m times after the nth character of expr1), the return value is 0.
INSTRB(expr1, expr2 [,n [,m]])
Works in the same way as INSTR, except that n and the return values
are expressed in bytes, rather than in characters. For a single-byte
character set, INSTRB is equivalent
to INSTR.
LEAST(expr, expr [,expr]...)
Compares the values of all the text string operands. Returns the first string in alphabetic order from among its operands.
LENGTH(expr)
Returns the number of characters in the text string
operand expr.
LENGTHB(expr)
Returns the length of expr in units of bytes.
LOWER(expr)
Converts a text string to lowercase.
LPAD(expr, n [,pad])
Returns the text string operand expr left-padded to length n with the sequence of characters in pad. The default value for pad is a blank.
If expr is longer than n, then LPAD returns
the portion of expr that fits in n.
These are a few examples of this expression:
/* A is set to 'XYXYXhello' */
A = LPAD ('hello, 10, 'XY')
/* A is set to 'hell' */
A = LPAD ('hello', 4 )LTRIM(expr [,set])
Returns the text string operand expr with all the left-most characters that appear in set removed. The default for set is a blank. If none of the left-most characters of expr appear in set, then LTRIM returns expr.
Examples:
/* A is set to 'def' */
A = LTRIM ('abcdef','abc')
/* A is set to 'abcdef' *
/A = LTRIM ('abcdef','bc')REPLACE(expr, search [,replacement])
Returns the text string operand expr with every occurrence of search replaced with replacement. If you
omit replacement, it removes all occurrences
of search. Use REPLACE to substitute one string for another or to remove
character strings.
Example:
/* Set A to 'BLACK and BLUE'. */
A = REPLACE('JACK and JUE', 'J', BL')RPAD(expr, n [,pad])
Returns the text string operand expr right-padded to length n with the sequence of characters in pad. The default value for pad is a blank.
If expr is longer than n, then RPAD returns
the portion of expr that fits in n.
Examples:
/* A is set to 'helloXYXYX' */
A = RPAD ('hello, 10, 'XY')
/* A is set to 'hell' */
A = RPAD ('hello', 4 )RTRIM(expr [,set])
Returns the text string operand expr with all the right-most characters that appear in set removed. The default value for set is a blank. If none of the right-most characters
of expr appear in set, then expr is returned.
Examples:
/* A is set to 'abc' */
A = RTRIM ('abcdef','def')
/* A is set to 'abcdef' */
A = RTRIM ('abcdef','de')SUBSTR(expr, m [,n]) or SUBSTRING(expr, m [,n])
SUBSTRING returns a
sub string of the text string operand expr of length n characters beginning at
the mth character. If n is negative, SUBSTR counts
backward of expr. If you omit the n, the sub string starts from m and finishes at the end of expr.
Example:
/* Check that the tax code starts with GG */
IF length(Tax_code) <= 2
THEN
(message = 'Tax code is too short'
RETURN message
)IF substr( Tax_code, 1, 2) = 'GG' THEN ...SUBSTRB((expr, m [,n])
The same as SUBSTR,
except that the arguments m and n are expressed in bytes, rather than in characters.
For a single-byte database character set, SUBSTRB is equivalent to SUBSTR.
TRANSLATE(expr,from,to)
Returns the text string operand expr with all occurrences of each character in from replaced by its corresponding character
in to. Characters in expr that aren't in from aren't
replaced. The argument from can contain
more characters than to. In this case,
the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in expr, they're removed from the return value.
TRIM(expr)
Trims leading and trailing spaces from a character string.
UPPER(expr)
Converts a text string to uppercase.
SPLIT FUNCTIONS
These Text Formula Functions return TEXT_NUMBER arrays. The arrays contain the result of splitting the string by a separator.
SPLIT_BY_SPACES
The separator is a sequence of one or more consecutive spaces. Here "space" includes tab character, newline in addition to space ' '.
SPLIT_BY_SPACES(expr)
Here's an example of SPLIT_BY_SPACES:
SPLIT_BY_SPACES(' Hello World !! ')The return value is an array containing 'Hello', 'World' in that order.
SPLIT
SPLIT(expr, separator, default_string)
Splits expr into strings, separated by separator, and returns the strings in a TEXT_NUMBER array. If there is nothing between two consecutive separators then default_string is returned in the array.
SPLIT Example 1:
SPLIT('Hello|World', '|', '_')The return value is an array containing 'Hello', 'World' in that order.
SPLIT Example 2 Multiple Character Separator:
SPLIT('Hello||World', '||', '_')The return value is an array containing 'Hello', 'World' in that order.
SPLIT('|Hello||World|', '|', '_')The return value is an array containing '_', 'Hello', '_', 'World', '_' in that order. The default string, '_', replaces "gaps" between separators i.e. before first '|', after second '|' and before 3rd '|', and after last '|'.
SPLIT('Hello', '||', '_')The return value is an array just containing 'Hello'.