A.5 Text Functions

Within the general category of text functions, the OLAP DML statements can be grouped into the following subcategories:

General character functions
Byte functions
Multiline functions

A.5.1 General Character Functions

Table A-5, "General Character Functions" lists the OLAP DML statements that you use to manipulate text based on characters.

Table A-5 General Character Functions

Statement	Description
ASCII	Returns the decimal representation of the first character of an expression.
BLANKSTRIP	Removes leading or trailing blank spaces from text values.
CHANGECHARS	Changes one or more occurrences of a specified string in a text expression to another string.
EXTCHARS	Extracts a portion of a text expression using characters.
FINDCHARS	Returns the character position of the beginning of a specified group of characters within a text expression.
GREATEST	Returns the largest expression in a list of expressions.
INITCAP	Returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase.
INSCHARS	Inserts one or more characters into a text expression.
INSTR	Searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring.
JOINCHARS	Joins two or more text values, as characters, as a single line.
LEAST	Returns the smallest expression in a list of expressions.
LIKECASE	Controls whether the LIKE operator is case sensitive.
LIKEESCAPE	An escape character for the LIKE operator.
LOWCASE	Converts all alphabetic characters in a text expression into lowercase.
LPAD	Returns an expression, left-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.
LTRIM	Removes characters from the left of a text expression, with all the leftmost characters that appear in another text expression removed.
MAXCHARS	The number of characters in the longest line of a multiline text expression. The result returned by MAXCHARS has the same dimensions as the specified expression.
NULLIF	Compares one expression with another and returns NA when the expressions are equal, or the base expression when they are not.
NUMCHARS	The number of characters in a text expression.
OBSCURE	Provides two mechanisms for encrypting a single-line text expression. Depending on the mechanism you use, OBSCURE can also restore the encrypted value to its original form.
REMCHARS	Removes one or more characters from a text expression and returns the value that remains.
REPLCHARS	Replaces one or more characters in a text expression.
RPAD	Returns an expression, right-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.
RTRIM	Removes characters from the right of a text expression, with all the rightmost characters that appear in another text expression removed.
SUBSTR	Returns a portion of string, beginning at a specified character position, and a specified number of characters long.
TEXTFILL	Reformats a text value to fit compactly into lines of a specified width, regardless of its current format.
TRIM	Removes leading or trailing characters (or both) from a character string.
UPCASE	Converts all alphabetic characters in a text expression into uppercase.

A.5.2 Byte Functions

Table A-6, "Byte Functions" lists the OLAP DML statements that you use to manipulate text based on bytes.

Table A-6 Byte Functions

Statement	Description
CHANGEBYTES	Changes one or more occurrences of a specified string in a text expression to another string.
EXTBYTES	Extracts a portion of a text expression using bytes.
FINDBYTES	Returns the byte position of the beginning of a specified group of bytes within a text expression.
INSBYTES	Inserts one or more bytes into a text expression.
INSTRB	Searches a string for a substring using bytes and returns the position in the string that is the first byte of a specified occurrence of the substring.
JOINBYTES	Joins two or more text values, as bytes, as a single line.
MAXBYTES	The number of bytes in the longest line of a multiline text expression.
NULLIF	The number of bytes in a text expression.
REMBYTES	Removes one or more bytes from a text expression and returns the value that remains.
REPLBYTES	Replaces one or more bytes in a text expression.
SUBSTRB	Returns a portion of string, beginning at a specified byte position, and a specified number of bytes long.

A.5.3 Multiline Text Functions

Table A-7, "MultiLine Text Functions" lists the OLAP DML statements that you use to manipulate multiline text.

Table A-7 MultiLine Text Functions

Statement	Description
CHARLIST	Transforms an expression into a multiline text value with a separate line for each value of the original expression.
EXTCOLS	Extracts specified columns from each line of a multiline text value.
EXTLINES	Extracts lines from a multiline text expression.
FILTERLINES	Applies a filter expression that you create to each line of a multiline text expression.
FINDLINES	Determines the position of one or more lines in a multiline text expression.
INLIST	Determines whether every line of a text value is a line in a second text value.
INSCOLS	Inserts into the columns of a multiline TEXT value all the columns of another TEXT value.
INSLINES	Inserts one or more lines into a multiline text expression.
JOINCOLS	Joins the corresponding lines of two or more multiline text values.
JJOINLINES	Joins the values of two or more text expressions into a single multiline value.
MAXBYTES	The number of bytes in the longest line of a multiline text expression.
NUMLINES	The number of lines in each value of a text expression. The result returned by NUMLINES has the same dimensions as the specified expression.
REMCOLS	Removes specified columns from every line of a multiline TEXT value.
REMLINES	Removes one or more lines from a multiline TEXT expression and returns the value that remains.
REPLCOLS	Replaces some or all of the character columns in one multiline TEXT value with the columns of another.
REPLLINES	Replaces one or more lines in a multiline text expression.
SORTLINES	Sorts the lines in a multiline TEXT value.
UNIQUELINES	Removes duplicate lines in a multiline TEXT value and sorts the lines in ascending order.