update special character columns dynamically for all columns of a table?

Question

I want to replace special characters by normal characters in all columns dynamically for all columns of a table.But it works only for a column which is hardcoded

 alter proc dbo.specialcharacterreplacer
@tblname varchar(1000),
@column_name varchar(1000)
as
begin

declare @Sql VARCHAR(MAX)
set @Sql = '
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ò'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ö'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ð'''+ ', '+'''o'''+') 
 exec (@sql)

end
go
EXEC dbo.specialcharacterreplacer @tblname = 'dirtyyyysource', @column_name ='select *from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '@tblname''

how to make columns dynamic?

Hi, did my answer help you solve your issue? Do you need further support? — Shnugo, Feb 22 '16 at 14:45

score 1 · Answer 1 · edited May 23 '17 at 12:07

This was the central code to get your update statement for all columns of a given table dynamically. Be aware of TABLE_SCHEMA and the column's type. You might use some additions in the WHERE part... (in my example you'd try to replace the INT column as well...)

And you might have a look here: https://stackoverflow.com/a/32048968/5089204

There you'll find one of my former answers to a similar question and shows an approach how to create a function which will replace several special characters in one go.

CREATE TABLE dbo.TestTable(ID INT,Test1 VARCHAR(100), Test2 VARCHAR(100));
GO

declare @tblname varchar(1000)='TestTable';
declare @tblschema varchar(1000)='dbo'; 

DECLARE @SqlCmd VARCHAR(MAX)= 'UPDATE ' + @tblname + ' SET ' +
(
    STUFF(
         (
            SELECT ',' + COLUMN_NAME + ' = REPLACE(' + COLUMN_NAME + ', ' + '''ó'', ''o'''+')'+ CHAR(10) --might need to use CHAR(13)+CHAR(10)
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA=@tblschema AND TABLE_NAME = @tblname
            FOR XML PATH('')
         ),1,1,'')  + ';'
);

SELECT @SqlCmd;
GO

DROP TABLE dbo.TestTable;
GO

The result:

UPDATE TestTable SET ID = REPLACE(ID, 'ó', 'o')
,Test1 = REPLACE(Test1, 'ó', 'o')
,Test2 = REPLACE(Test2, 'ó', 'o')
;

@sql_lover *Not working !!!* is not enough to help you... Please explain: What is not working? What is the error message? — Shnugo, Feb 23 '16 at 08:03

update special character columns dynamically for all columns of a table?

1 Answers1