SQL REPLACE Function

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

The syntax of the SQL string Replace function 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 one.
• Replacing_Text: New sentence you want to embed into Expression.

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

SQL String Replace Function Example

This String Function changes 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 assign 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 the Email column in Server.

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

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

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