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).
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. |
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)
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, 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 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.