主页 / dba / 问题 / 232179
Accepted
Mark D
Asked: 2019-03-15 10:41:54 +0800 CST

从可能会或可能不会多次出现的列中删除文本

  • 772

我在 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 + ')'; 但看起来更新已在表中的某些但不是所有值上运行。

更新语句是什么来更新所有行以使它们具有正确的格式？请注意我有数百行，这只是为了修复先前更新引起的早期问题。查询只需要运行一次。

sql-server sql-server-2008

1 个回答

  1. Best Answer

    是这样的吗？

    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)
    • 1

相关问题