SQL REPLACE Function

The SQL Replace function is used to replace the existing string with a new substring 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

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. This function will substitute a portion or word present in this String_Expression.
• String_you want to change: Anything you put here, this Function will substitute it with a new.
• 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 String Replace Function Example

The SQL String Replace Function change the original string with the specified new one. The following query will show multiple ways to use this function.

In this example query, we declare a variable and assigning the data. First, we will supersede the T-SQL with SQL from the variable @String_Expression. Next, we used it on the T-SQL with SQL from ‘Learn T-SQL Server 2014’ directly

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

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

SELECT REPLACE ('Learn T-SQL Server 2014', 'T-SQL','SQL') AS 'SQLReplace'

It also allows you to change the column values or text. In this Function example, We are going to replace the .com with .org from Email column in Server.

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

In this example, We are going to supersedes the @ symbol present in Email column with an Empty space.

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