1

I have the following table in 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);        

Ideally I'd have OrgUnitName appear like OrgUnitName + ' (' + OrgUnitKey + ')'; But it appears like that update has been run on some but not all the values in the table.

What would the update statement be to update all rows so they would have the correct format? Please note I have hundreds of rows and this is only to fix an earlier issue a previous update caused. The query only needs to be run once.

1

Something like this?

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

View the result

SELECT  OrgUnitName from dbo.OrgUnits;

Result

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
    Spectacular. Worked like a charm. Thanks. If you don't mind could you explain it, just so I have the knowledge. – Mark D Mar 14 at 19:37
  • 1
    Hey @MarkD no problem! Well, the replace() takes care of each OrgUnitKey currently inside the OrgUnitName and the corresponding parentheses, and turns them into empty strings. After that there are some trailing spaces, they get romeved with the RTRIM(). At this point the OrgUnitName should be clear of OrgUnitKey's and should be uniform accross all records. From here we we can add the OrgUnitKey again, together with the parentheses. – Randi Vertongen Mar 14 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.