STR_REPLACE function [String]

Function

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

Syntax

REPLACE ( string_expr1, string_expr2 , string_expr3 )

Parameters

string_expr1 is the source string, or the string expression to be searched, expressed as CHAR, VARCHAR, UNICHAR, UNIVARCHAR, VARBINARY, or BINARY data type.

string_expr2 is 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 is the replacement string expression, expressed as CHAR, VARCHAR, UNICHAR, UNIVARCHAR, VARBINARY, or BINARY data type.

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)

Example 3

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)

Usage

Permissions

Any user can execute STR_REPLACE.

Standards and compatibility

ANSI SQL – Compliance level: Transact-SQL extension

See also

Data types CHAR, VARCHAR, UNICHAR, UNIVARCHAR, VARBINARY, or BINARY. See Chapter 4, “SQL Data Types.”

Function “LENGTH function [String]”.

For general information about string functions, see “String functions”.