How can I use the REPLACE function to change these two words using SQL?-CodePudding

I am using SQL Server and SSMS. I have two particular values I am trying to replace:

'South Manchester' to 'King's Landing'
'Manchester' to 'Highgarden'

I have the following syntax:

UPDATE Correspondence SET [Name] =
REPLACE(REPLACE([Name], 'Manchester', 'Highgarden') , 'South Manchester',' King’s Landing')

I end up with names like 'SouthHigarden'. Does anyone know how I can solve this problem?.

Thanks in advance.

CodePudding user response：

You need only to order the updates right, so that Manchester alone is te last to update

CREATE TABLE Correspondence (name varchar(100))
GO

INSERT INTO Correspondence VALUES ('South Manchester' ),('Manchester' )
GO

UPDATE Correspondence SET [Name] =
REPLACE(REPLACE([Name], 'South Manchester',' King’s Landing'), 'Manchester', 'Highgarden') 
GO

SELECT * FROM Correspondence
GO

| name              |
| :---------------- |
|  King’s Landing |
| Highgarden        |

db<>fiddle here

CodePudding user response：

You need to use a condition with a WHERE statement. Also, I noticed you might have the quotation marks not used properly.

Try this:

UPDATE Correspondence SET [Name] = "King’s Landing"
WHERE [NAME] = "South Manchester"

UPDATE Correspondence SET [Name] = "Highgarden"
WHERE [NAME] = "Manchester"

Hope that helps!