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 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.
- Replacing_Text: New sentence you want to embed into Expression.
For this String REPLACE 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'
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]