Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

B28126-03
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
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

REPLCHARS

The REPLCHARS function replaces one or more characters in a text expression.

Tip:

When you are using a multibyte character set, you can use the REPLBYTES function instead of the REPLCHARS function. Also, you can use the CHANGECHARS function to change one or more occurrences of a specified string in a text value to another string.

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

Syntax

REPLCHARS(text-expression characters [start])

Arguments

text-expression

The expression in which characters are to be replaced. When the characters to be replaced from text-expression contain embedded line breaks, these breaks are removed. Other line breaks are preserved. Removed line breaks are not counted toward the total number of characters replaced. Line breaks in the replacement expression are retained in the output of REPLCHARS, but are likewise not counted.

characters

A text expression that contains one or more characters that will replace existing characters in text-expression.

start

An INTEGER that represents the character position at which to begin replacing characters. The position of the first character in text-expression is 1. When you omit this argument, REPLCHARS starts with the first character. REPLCHARS replaces as many characters of text-expression as are required for the specified new characters. When the value of start is greater than the length of text-expression, REPLCHARS simply returns text-expression.

Examples

Example 8-76 Replacing Text Characters

This example shows how to replace a portion of the text value Hello there, Joe.

The statement

SHOW REPLCHARS('Hello there, Joe', 'Jane', 14)

produces the following output.

Hello there, Jane

Example 8-77 How REPLCHARS Handles Line Breaks

This example shows how REPLCHARS preserves but ignores line breaks.

var1 = JOINLINES('Hello' 'there' 'Joe')
var2 = JOINLINES('Hi' 'Jane')

The statement

show REPLCHARS(var1 var2)

produces the following output.

Hi
Janehere
Joe

REPLCHARS has replaced the first 6 characters of var1 (Hellot of HellothereJoe) with the 6 characters of var2 (HiJane). It has preserved the line breaks following Hi (from var2) and there (from var1).

To replace all 13 characters in var1, you must specify 13 replacement characters; for example, you can add 7 spaces after Jane.

var2 = JOINLINES('Hi' 'Jane       ')

The statement

SHOW REPLCHARS(var1 var2)

produces the following output.

Hi
Jane