SQL REPLACE Function

The SQL Replace function is one of the String Function, which is used to replace the existing string with a new string value.

In general, Sql Server Replace Function return VARCHAR output but if one of the input value is NVARCHAR then, it will return NVARCHAR output

String Replace in SQL Server Syntax

The basic syntax of the string Replace in SQL Server is as shown below:

SELECT REPLACE (String_Expression, String You want to change, Replacing_String)
FROM [Source]

• String_Expression: Please specify the valid string to perform a search. Replace function will replace a portion of a string present in this String_Expression.
• String you want to change: Please specify the valid string you want to change. Whatever you place here, REPLACE Function will replace it with a new string
• Replacing_String: Please specify the valid new string you want to insert into String_Expression.

In this article, we will show you, How to write a String REPLACE in SQL Server with an example. For this demonstration, We are going to use the below-shown data

Sql Server String Replace Function Example 1

The String Replace Function simply replaces the original string with the specified new string. The following query will show multiple ways to use Replace function.

DECLARE @String_Expression varchar(50)
SET @String_Expression = 'Learn T-SQL Server'

SELECT REPLACE (@String_Expression, 'T-SQL','SQL') AS 'SQL Replace' 

--Replacing String Directly
SELECT REPLACE ('Learn T-SQL Server 2014', 'T-SQL','SQL') AS 'SQL Replace'

OUTPUT

ANALYSIS

Within this string replace function example query, the below lines of code is used to declare a string variable and assigning the string data.

DECLARE @String_Expression varchar(50)
SET @String_Expression = 'Learn T-SQL Server'

In below statement, We used Replace function to replace the T-SQL with SQL from the variable @String_Expression. We also assigned new name to that result using ALIAS Column.

SELECT REPLACE (@String_Expression, 'T-SQL','SQL') AS 'SQL Replace' 

In the next line, We used sql string replace function to replace the T-SQL with SQL from ‘Learn T-SQL Server 2014’ string directly

--Replacing String Directly
SELECT REPLACE ('Learn T-SQL Server 2014', 'T-SQL','SQL') AS 'SQL Replace'

String Replace Function Example 2

The Replace String function also allows you to replace the column values. In this example, We are going to replace the .com with .org from Email column

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,REPLACE ([Email], 'com','org' ) AS [SQL Replace]
 FROM [Employe]

OUTPUT

String Replace Example 3

In this example, We are going to replace the @ symbol present in Email column with an Empty space using this Sql Sever Replace function.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
      ,REPLACE ([Email], '@','  ' ) AS [SQL Replace]
 FROM [Employe]

OUTPUT