How can I update multiple columns with a Replace in SQL server?

Question

How do I update different columns and rows across a table? I want to do something similiar to replace a string in SQL server

I want to do this but the value exists in multiple columns of the same type. The values are foreign keys varchars to an employee table. Each column represents a task, so the same employee may be assigned to several tasks in a record and those tasks will vary between records. How can I do this effectively? Basically something of a replace all accross varying columns throughout a table.

Thanks for any help or advice.

Cheers, ~ck in San Diego

Solution 1

This should do the trick:

UPDATE table1
SET field1 = replace(field1, 'oldstring', 'newstring'),
    field2 = replace(field2, 'oldstring2', 'newstring2')

etc...

Solution 2

The main idea is to create a SQL Update sentence, no matter how many fields has the table. It was created on SQL Server 2012, however I think it works on 2008 too.

Sample table:

CREATE TABLE SampleTable
(
    Field1 INT,
    Field2 VARCHAR(20),
    Field3 VARCHAR(20),
    Field4 VARCHAR(100),
    Field5 DATETIME,
    Field6 NVARCHAR(10)
);

Get only varchar and nvarchar fields. Change OLD_TEXT and NEW_TEXT accord to your requirement. Change system_type_id values if you need match not only varchar and nvarchar fields.

SELECT 'UPDATE dbo.SampleTable SET ' + STUFF((SELECT ', [' + name + '] =   REPLACE([' + name + '], ''OLD_TEXT'', ''NEW_TEXT'')' 
FROM sys.COLUMNS
WHERE 
    [OBJECT_ID] = OBJECT_ID('SampleTable')
    AND [is_identity] = 0 --It's not identity field
    AND [system_type_id] in (167, 231) -- varchar, nvarchar
FOR XML PATH('')), 1,1, '')

The result of the last query is:

UPDATE dbo.SampleTable SET  
    [Field2] = REPLACE([Field2], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field3] = REPLACE([Field3], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field4] = REPLACE([Field4], 'OLD_TEXT', 'NEW_TEXT'), 
    [Field6] = REPLACE([Field6], 'OLD_TEXT', 'NEW_TEXT');

just copy the result and execute in SSMS. This snippet saves you a little time when writing the update sentence.

Hope it helps.

Comments

[Hcabnettek]

and its ok if the string doesn't exist in the column? ok this sounds cool. I would just apply it accross all possible columns??? Thanks for the tip. i will try this. Sweet!

[Lance Roberts]

Please don't try it right out on the production system (or at least do a backup first).

[Lance Roberts]

Also, you can use a where clause to narrow things down a lot (probably).

[Hcabnettek]

Just to update any interested parties; I ran into a bit of an issue with this. The datatype is varchar(7), if 'newstring' is same length or shorter as 'oldstring' all is well, but when I was trying to go the other direction, data will be truncated errors reared their ugly head. A less effecient way came to mind and that was to run an update on each column in the stored proc like Begin Update table1 SET field1='newstring' WHERE field1='oldstring' END, then do the same query for field2, field3, filedn. I am sure it is not effecient, but the result is what I was seeking. Thanks all!

[Jason Kester]

Ah, then all bets are off :) For what it's worth though, this is a much bigger deal than "a little denormalized". It's the First Normal Form that's being violated, not the 3rd. There are circumstances that call for flattening tables in a way that duplicates data, but that's not what's happening here. This is just pain generation for no benefit.

[gibbz00]

awesome solution!