STR_REPLACE Function [String]

Takes three arguments as input of type BINARY or STRING and replaces any instances of the second string expression (string_expr2) that occur within the first string expression (string_expr1) with a third expression (string_expr3).

Syntax

REPLACE ( string_expr1, string_expr2, string_expr3 )

Parameters

Parameter	Description
string_expr1	The source string, or the string expression to be searched, expressed as `CHAR`, `VARCHAR`, `UNICHAR`, `UNIVARCHAR`, `VARBINARY`, or `BINARY` data type.
string_expr2	The pattern string, or the string expression to find within the first expression (`string_expr1`) and is expressed as `CHAR`, `VARCHAR`, `UNICHAR`, `UNIVARCHAR`, `VARBINARY`, or `BINARY` data type.
string_expr3	The replacement string expression, expressed as `CHAR`, `VARCHAR`, `UNICHAR`, `UNIVARCHAR`, `VARBINARY`, or `BINARY` data type.

Remarks

STR_REPLACE is an alias of REPLACE function.

Takes any data type as input and returns STRING or BINARY.

For example, an empty string passed as an argument (“”) is replaced with one space (“ ”) before further evaluation occurs. This is true for both BINARY and STRING types.
All arguments can have a combination of BINARY and STRING data types.
The result length may vary, depending upon what is known about the argument values when the expression is compiled. If all arguments are columns or host variables assigned to constants, SAP Sybase IQ calculates the result length as:
```
result_length = ((s/p)*(r-p)+s)
WHERE
  s = length of source string
  p = length of pattern string
  r = length of replacement string
IF (r-p) <= 0, result length = s
```
If SAP Sybase IQ cannot calculate the result length because the argument values are unknown when the expression is compiled, the result length used is 255.
RESULT_LEN never exceeds 32767.

Standards and Compatibility

SQL—Transact-SQL extension to ISO/ANSI SQL grammar.

Example

Replaces the string def within the string cdefghi with yyy.

select replace("cdefghi", "def", "yyy")
-------------
cyyyghi
(1 row(s) affected)

Replaces all spaces with “toyota”

select str_replace ("chevy, ford, mercedes", "","toyota")
----------
chevy,toyotaford,toyotamercedes
(1 row(s) affected)

Accepts NULL in the third parameter and treats it as an attempt to replace string_expr2 with NULL, effectively turning STR_REPLACE into a “string cut” operation. Returns “abcghijklm”:

select str_replace("abcdefghijklm", "def", NULL)
----------
abcghijklm
(1 row affected)