Replaces any instances of the second string expression (string_expression2) that occur within the first string expression (string_expression1) with a third expression (string_expression3).


str_replace("string_expression1", "string_expression2", "string_expression3")




  • Returns varchar data if string_expression (1, 2, or 3) is char or varchar.

  • Returns univarchar data if string_expression (1, 2, or 3) is unichar or univarchar.

  • Returns varbinary data if string_expression (1, 2, or 3) is binary or varbinary.

  • All arguments must share the same datatype.

  • If any of the three arguments is NULL, the function returns null.

    str_replace accepts NULL in the third parameter and treats it as an attempt to replace string_expression2 with NULL, effectively turning str_replace into a “string cut” operation.

    For example, the following returns “abcghijklm”:

    str_replace("abcdefghijklm", "def", NULL)
  • The result length may vary, depending upon what is known about the argument values when the expression is compiled. If all arguments are variables with known constant values, the SAP ASE server calculates the result length as:

    result_length = ((s/p)*(r-p)+s)
    s = length of source string
    p = length of pattern string
    r = length of replacement string
    if (r-p) <= 0, result length = s
  • If the source string (string_expression1) is a column, and string_expression2 and string_expression3 are constant values known at compile time, the SAP ASE server calculates the result length using the formula above.

  • If the SAP ASE server cannot calculate the result length because the argument values are unknown when the expression is compiled, the result length used is 255, unless traceflag 244 is on. In that case, the result length is 16384.

  • result_len never exceeds 16384.


ANSI SQL – Compliance level: Transact-SQL extension.


Any user can execute str_replace.

Related concepts
System and User-Defined Datatypes