SQL REPLACE Function

The SQL Replace function is a String Function 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 the string replace function will return NVARCHAR output

String Replace in SQL Server Syntax

The syntax of the string Replace in SQL Server is

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

• String_Expression: String to perform a search. Replace function will replace a portion of a string present in this String_Expression.
• String you want to change: Anything you put here, REPLACE Function will replace it with a new string
• Replacing_String: New string you want to embed into String_Expression.

For this String REPLACE in SQL Server demonstration, We use the below data

Sql Server String Replace Function Example 1

The String Replace Function replaces the original string with the specified new string. The following query will show multiple ways to use the 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'

Within this string replace function example query, we declare a string variable and assigning the string data.

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

Here, we will replace the T-SQL with SQL from the variable @String_Expression.

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

Next, we used the 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 String Function example, We are going to replace the .com with .org from Email column in SQL Server.

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

String Replace Example 3

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

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