Replace string in SQL Server
Let us suppose we need to update or replace any string value in any table, we could use the below methods –
- Replace String in SQL Server Example :
In below example, we have a string variable, and then we are going to replace a part of a string with a new string using the Replace Function.
SQL Server Query to Replace String –
DECLARE @String_Value varchar(50) SET @String_Value = 'This provides free and excellent knowledge on SQL Server.' SELECT REPLACE (@String_Value, 'This', 'w3wiki');
Output :
w3wiki provides free and excellent knowledge on SQL Server.
Let us suppose we have below table named “geek_demo” :
Name | Salary | City | |
---|---|---|---|
Ankit | 24500 | Delhi | ankit@xyz.com |
Babita | 23600 | Noida | babita@xyz.com |
Chetan | 25600 | Noida | chetan@xyz.com |
Deepak | 24300 | Delhi | deepak@xyz.com |
Isha | 25900 | Delhi | isha@xyz.com |
Khushi | 24600 | Noida | khushi@xyz.com |
Megha | 25500 | Noida | megha@xyz.com |
Parul | 23900 | Noida | parul@xyz.com |
- Replace String Example :
In below example, we will replace a string in SQL Server SELECT Statement using the REPLACE Function while selecting data from the SQL Server table.
SQL Server Query to replace part of a string –
SELECT TOP 1000 [Name], [Salary], [City], [email], REPLACE([email], 'xyz.com', 'gfg.org') AS [New EmailID] FROM [geek_demo]
Output :
Name | Salary | City | New EmailID | |
---|---|---|---|---|
Ankit | 24500 | Delhi | ankit@xyz.com | ankit@gfg.org |
Babita | 23600 | Noida | babita@xyz.com | babita@gfg.org |
Chetan | 25600 | Noida | chetan@xyz.com | chetan@gfg.org |
Deepak | 24300 | Delhi | deepak@xyz.com | deepak@gfg.org |
Isha | 25900 | Delhi | isha@xyz.com | isha@gfg.org |
Khushi | 24600 | Noida | khushi@xyz.com | khushi@gfg.org |
Megha | 25500 | Noida | megha@xyz.com | megha@gfg.org |
Parul | 23900 | Noida | parul@xyz.com | parul@gfg.org |
- Replace String in SQL Example :
In the below example, we will replace string in SQL UPDATE Statement using the REPLACE Function in Update Statement.
SQL Server Query to replace part of a string –
UPDATE [geek_demo] SET [email] = REPLACE([email], 'xyz.com', 'gfg.org');
Result :
(8 row(s) affected)
Now let us see the Updated table –
SELECT TOP 1000 [Name], [Salary], [City], [email] FROM [geek_demo];
Output :
Name | Salary | City | |
---|---|---|---|
Ankit | 24500 | Delhi | ankit@gfg.org |
Babita | 25600 | Noida | babita@gfg.org |
Chetan | 25600 | Noida | chetan@gfg.org |
Deepak | 24300 | Delhi | deepak@gfg.org |
Isha | 25900 | Delhi | isha@gfg.org |
Khushi | 24600 | Noida | khushi@gfg.org |
Megha | 25500 | Noida | megha@gfg.org |
Parul | 23900 | Noida | parul@gfg.org |