Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

A.5 Text Functions

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

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.