Chapter 2: Transact-SQL Functions

str_replace

Description

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

Syntax

replace("string_expression1", "string_expression2", "string_expression3")

Parameters

string_expression1: is the source string, or the string expression to be searched, expressed as char, varchar, unichar, univarchar, varbinary, or binary datatype.

string_expression2: is the pattern string, or the string expression to find within the first expression (string_expression1). string_expression2 is expressed as char, varchar, unichar, univarchar, varbinary, or binary datatype.

string_expression3: is the replacement string expression, expressed as char, varchar, unichar, univarchar, binary, or varbinary datatype.

Examples

Example 1

Replaces the string def within the string cdefghi with yyy.

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

Example 2

Replaces all spaces with "toyota".

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

Adaptive Server converts an empty string constant to a string of one space automatically, to distinguish the string from NULL values.

Example 3

Returns “abcghijklm”:

select str_replace("abcdefghijklm", "def", NULL)

----------
abcghijklm
(1 row affected)

Usage

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, Adaptive Server 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 the source string (string_expression1) is a column, and string_expression2 and string_expression3 are constant values known at compile time, Adaptive Server calculates the result length using the formula above.
If Adaptive 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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute str_replace.