REPLACE

The REPLACE function replaces all occurrences of a substring within a string and returns the new string.

Syntax

REPLACE(subjectStr, searchStr, replaceStr)

subjectStr

The string you want modified. This can be a literal string or a reference to a char or varchar value.

searchStr

The substring to replace within subjectStr. This can be a literal string or a reference to a char or varchar value.

replaceStr

The replacement substring. This can be a literal string or a reference to a char or varchar value.

Results

A string value.

Examples

The first examples shows the players on each team with averages greater than .300. The second example shows the result of replacing the team of those players with averages greater than 0.300 who play on one team (the Cards):

splice> SELECT DisplayName, Average, Team
   FROM Players JOIN Batting on Players.ID=Batting.ID
   WHERE Average > 0.300 AND Games>50;

DISPLAYNAME             |AVERAGE  |TEAM
------------------------------------------
Buddy Painter           |0.31777  |Giants
John Purser             |0.31151  |Giants
Kelly Tamlin            |0.30337  |Giants
Stan Post               |0.30472  |Cards

4 rows selected

splice> SELECT DisplayName, Average,
    REPLACE(Team, 'Cards', 'Giants') "TRADED"
    FROM PLAYERS JOIN Batting ON Players.ID=Batting.ID
    WHERE Team='Cards' AND Average > 0.300 AND Games > 50;

DISPLAYNAME             |AVERAGE  |TRADED
------------------------------------------
Stan Post               |0.30472  |Giants

1 row selected

See Also