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'
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]