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

STR_REPLACE is an alias of REPLACE function

REPLACE ( string_expr1, string_expr2, string_expr3 )

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)

• 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.

Any user can execute STR_REPLACE.

ANSI SQL – Compliance level: Transact-SQL extension