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 n
th character for the m
th 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 n
th 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 m
th 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'.