SQL REPLACE Function

The SQL Replace function is used to replace the existing string with a new substring value. In general, the Replace Function returns VARCHAR output. But if one of the input values is NVARCHAR, then it will return NVARCHAR output. The syntax of the string Replace function in SQL Server is

SELECT REPLACE (Expression, Text_You want to change, Replacing_Text)
FROM [Source]

• Expression: String to perform a search. This function will substitute a portion or word present in this Expression.
• Text_you want to change: Anything you put here, this Function will substitute it with a new.
• Replacing_Text: New sentence you want to embed into 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 text 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 Anything with Everything from the variable @Expression. Next, we used it on the Hello with New from ‘Welcome to Hello World’ directly

DECLARE @Expression varchar(50)
SET @Expression = 'Learn Anything From Here'

SELECT REPLACE (@Expression, 'Anything','Everything') AS 'Result' 

SELECT REPLACE ('Welcome to Hello World', 'Hello','New') AS 'Result'

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 [Output]
 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 [Result]
 FROM [Employe]