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

Employee Source

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'
Simple Example 1

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]
      ,REPLACE ([Email], 'com','org' ) AS [Output]
 FROM [Employe]
SQL REPLACE Function 2

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

SELECT [FirstName]
      ,REPLACE([Email], '@','  ' ) AS [Result]
 FROM [Employe]
SQL REPLACE Function on Email IDs 3

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.