從可能出現也可能不出現多次的列中刪除文本

我在 SQL Server 2008 中有下表。

create table OrgUnits
(
   OrgUnitId int not null constraint PK_OrgUnits1 primary key,
   OrgUnitName varchar(100),
   OrgUnitLevel int not null,
   OrgUnitKey varchar(100) constraint UQ_OrgUnits_OrgUnitKey unique,
   Level1Id int not null,
   Level2Id int,
   StatusId int not null constraint FK_OrgUnits_Status references Status,
   VersionNumber int not null
);

INSERT INTO OrgUnits (OrgUnitId, OrgUnitName, OrgUnitKey, Level1Id, Level2Id,
                     StatusId, VersionNumber) 
VALUES 
(218824, 'Company1 (5) (5) (5)', '5', 218824, 1, 1),
(218825, 'Division1 (1) (1) (1)', '1', 218825, 1, 1),
(218826, 'XAVAT (4) (4) (4)', '4', 218826, 1, 1),
(218827, 'WATe (7) (7) (7)', '7', 218827, 1, 1),
(218828, 'Communications', '05-905-6320-300', 218824, 218828, 1, 1),
(218829, 'Corporate Development', '05-955-6320-300', 218824, 218829, 1, 1),
(218830, 'Corporate Board of Directors', '05-111-3515-301', 218830, 1, 1);        

理想情況下，我會OrgUnitName看起來像OrgUnitName + ' (' + OrgUnitKey + ')'; 但似乎更新已經在表中的一些但不是所有值上執行。

更新語句將如何更新所有行以使它們具有正確的格式？請注意，我有數百行，這只是為了解決先前更新引起的早期問題。查詢只需要執行一次。

像這樣的東西？

UPDATE dbo.OrgUnits
SET OrgUnitName = RTRIM(REPLACE(OrgUnitName,'('+OrgUnitKey+')',''))+' (' + OrgUnitKey + ')';

查看結果

SELECT  OrgUnitName from dbo.OrgUnits;

結果

OrgUnitName
Company1 (5)
Division1 (1)
XAVAT (4)
WATe (7)
Communications (05-905-6320-300)
Corporate Development (05-955-6320-300)
Corporate Board of Directors (05-111-3515-301)

引用自：https://dba.stackexchange.com/questions/232179