By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--
-- Sample data
--
create table Document (
document_id int identity primary key,
content nvarchar(max)
);
insert into Document (content) values
('[{"id":1, "code":"abc123"},{"id":2, "code":"def456"}]')
, ('{"id":3, "data":{"name":"test",code1":"abc123","code2":"abc123"}}');
create table RenameMatches (
WrongValue nvarchar(30) not null primary key,
NewValue nvarchar(30) not null
);
insert into RenameMatches (WrongValue, NewValue) values
('abc123', 'abc12345')
, ('def456', 'def456789');
4 rows affected
-- Using a table variable with identity
DECLARE @RenameMatches table (
id INT identity primary key,
WrongValue NVARCHAR(30) not null,
NewValue NVARCHAR(30) not null
);
INSERT INTO @RenameMatches (WrongValue, NewValue)
SELECT WrongValue, NewValue
FROM RenameMatches;
DECLARE @MaxRenameID INT = (SELECT MAX(id) FROM @RenameMatches);
DECLARE @RenameID INT = 1;
WHILE @RenameID <= @MaxRenameID
BEGIN
UPDATE d
SET d.Content = REPLACE(d.Content
, quotename(rm.WrongValue,'"')
, quotename(rm.NewValue,'"'))
FROM Document d
JOIN @RenameMatches rm
ON rm.id = @RenameID
AND d.content like '%"'+rm.WrongValue+'"%';
SET @RenameID += 1;
END;
5 rows affected
select * from Document
| document_id | content |
|---|---|
| 1 | [{"id":1, "code":"abc12345"},{"id":2, "code":"def456789"}] |
| 2 | {"id":3, "data":{"name":"test",code1":"abc12345","code2":"abc12345"}} |