How to Use the REPLACE() Function in SQL Server: A Complete Guide

REPLACE function replaces all occurrences of a specified string value with another string value.

Syntax:

<span class="pln">
REPLACE</span><span class="pun">(</span><span class="typ">String_Expression</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Pattern</span><span class="pln"> </span><span class="pun">,</span><span class="pln"> </span><span class="typ">Replacement_Value</span><span class="pun">)</span><span class="pln">
</span>

Example: All .COM strings are replaced with .NET

Code:

<span class="pln">

</span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Email</span><span class="pun">,</span><span class="pln"> REPLACE</span><span class="pun">(</span><span class="typ">Email</span><span class="pun">,</span><span class="pln"> </span><span class="str">'.com'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'.net'</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">Email</span><span class="pln">
</span><span class="kwd">from</span><span class="pln">  </span><span class="typ">TableEmployee</span><span class="pln">

</span>

Prerequisite Code

<span class="pln">
CREATE TABLE </span><span class="typ">TableEmployee</span><span class="pun">(</span><span class="pln">
	</span><span class="typ">FirstName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
	</span><span class="typ">LastName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
	</span><span class="typ">Email</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">

INSERT INTO </span><span class="typ">TableEmployee</span><span class="pln"> VALUES</span><span class="pun">(</span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Azmi'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo@aaa.com'</span><span class="pun">)</span><span class="pln">
INSERT INTO </span><span class="typ">TableEmployee</span><span class="pln"> VALUES</span><span class="pun">(</span><span class="str">'Azam'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ali'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Azam@aaa.com'</span><span class="pun">)</span><span class="pln">
INSERT INTO </span><span class="typ">TableEmployee</span><span class="pln"> VALUES</span><span class="pun">(</span><span class="str">'Inza'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Hoque'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo@aaa.com'</span><span class="pun">)</span><span class="pln">
INSERT INTO </span><span class="typ">TableEmployee</span><span class="pln"> VALUES</span><span class="pun">(</span><span class="str">'Jaman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Sk'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Jaman@aaa.com'</span><span class="pun">)</span><span class="pln">
INSERT INTO </span><span class="typ">TableEmployee</span><span class="pln"> VALUES</span><span class="pun">(</span><span class="str">'Samser'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Alam'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Samser@aaa.com'</span><span class="pun">)</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">TableEmployee</span><span class="pln">
</span>