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)

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

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute str_replace.

See also

Datatypes char, varchar, binary, varbinary, unichar, univarchar

Functions length