Tutorial Gateway

SQL REPLACE Function

by

The SQL REPLACE is one of the SQL String Function, which is used to replace the existing string with a new string value. In general, 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 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 REPLACE in SQL Server with an example. For this, We are going to use the below-shown data

REPLACE IN SQL 2014

SQL String REPLACE Function Example 1

The 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
SQL REPLACE FUNCTION 1

ANALYSIS

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 as ‘SQL Replace’ using ALIAS Column.

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

In the next line, We used 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'

SQL Server REPLACE Function Example 2

The REPLACE String function in SQL 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
SQL REPLACE FUNCTION 2

String REPLACE in SQL Example

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

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

OUTPUT
REPLACE in SQL 3

Thank You for Visiting Our Blog

Placed Under: SQL