Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

REPLACESTR

REPLACESTR

Replaces characters in a string with a single character, multiple characters, or no character. REPLACESTR searches the input string for all strings you specify and replaces them with the new string you specify.

Syntax

REPLACESTR (
CaseFlag
,
InputString
,
OldString
1, [OldString2, ... OldStringN,]
NewString
)
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
CaseFlag
Required
Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression.
When
CaseFlag
is a number other than 0, the function is case sensitive.
When
CaseFlag
is a null value or 0, the function is not case sensitive.
InputString
Required
Must be a character string. Passes the strings you want to search. You can enter any valid transformation expression. If you pass a numeric value, the function converts it to a character string.
If
InputString
is NULL, REPLACESTR returns NULL.
OldString
Required
Must be a character string. The string you want to replace. You must enter at least one
OldString
argument. You can enter one or more characters per
OldString
argument. You can enter any valid transformation expression. You can also enter a text literal enclosed within single quotation marks, for example, 'abc'.
If you pass a numeric value, the function converts it to a character string.
When REPLACESTR contains multiple
OldString
arguments, and one or more
OldString
arguments is NULL or empty, REPLACESTR ignores the
OldString
argument. When all
OldString
arguments are NULL or empty, REPLACESTR returns
InputString
.
The function replaces the characters in the
OldString
arguments in the order they appear in the function. For example, if you enter multiple
OldString
arguments, the first
OldString
argument has precedence over the second
OldString
argument, and the second
OldString
argument has precedence over the third
OldString
argument. When REPLACESTR replaces a string, it places the cursor after the replaced characters in
InputString
before searching for the next match.
NewString
Required
Must be a character string. You can enter one character, multiple characters, an empty string, or NULL. You can enter any valid transformation expression.
If
NewString
is NULL or empty, REPLACESTR removes all occurrences of
OldString
in
InputString
.

Return Value

String.
Empty string if REPLACESTR removes all characters in
InputString
.
NULL if
InputString
is NULL.
InputString
if all
OldString
arguments are NULL or empty.

Examples

The following expression removes the double quotes and two different text strings from web log data for each row in the WEBLOG port:
REPLACESTR( 1, WEBLOG, '"', 'GET ', ' HTTP/1.1', NULL )
WEBLOG
RETURN VALUE
"GET /news/index.html HTTP/1.1"
/news/index.html
"GET /companyinfo/index.html HTTP/1.1"
/companyinfo/index.html
GET /companyinfo/index.html
/companyinfo/index.html
GET
[empty string]
NULL
NULL
The following expression changes the title for certain values for each row in the TITLE port:
REPLACESTR ( 1, TITLE, 'rs.', 'iss', 's.' )
TITLE
RETURN VALUE
Mrs.
Ms.
Miss
Ms.
Mr.
Mr.
MRS.
MRS.
The following expression changes the title for certain values for each row in the TITLE port:
REPLACESTR ( 0, TITLE, 'rs.', 'iss', 's.' )
TITLE
RETURN VALUE
Mrs.
Ms.
MRS.
Ms.
The following expression shows how the REPLACESTR function replaces multiple
OldString
arguments for each row in the INPUT port:
REPLACESTR ( 1, INPUT, 'ab', 'bc', '*' )
INPUT
RETURN VALUE
abc
*c
abbc
**
abbbbc
*bb*
bc
*
The following expression shows how the REPLACESTR function replaces multiple
OldString
arguments for each row in the INPUT port:
REPLACESTR ( 1, INPUT, 'ab', 'bc', 'b' )
INPUT
RETURN VALUE
ab
b
bc
b
abc
bc
abbc
bb
abbcc
bbc
When you want to use a single quote (') in either
OldString
or
NewString
, you must use the CHR function. Use both the CHR and CONCAT functions to concatenate a single quote onto a string. The single quote is the only character that cannot be used inside a string literal. Consider the following example:
CONCAT( 'Joan', CONCAT( CHR(39), 's car' ))
The return value is:
Joan's car
The following expression changes a string that includes the single quote, for each row in the INPUT port:
REPLACESTR ( 1, INPUT, CONCAT('it', CONCAT(CHR(39), 's' )), 'its' )
INPUT
RETURN VALUE
it's
its
mit's
mits
mits
mits
mits'
mits'