How to loop through tables and columns and replace ',' with ';'

I must warn you not to do this too blind, there could be a ',' somewhere where you don't want it to change.


Where do you find columns?

The join of INFORMATION_SCHEMA.COLUMNS  and INFORMATION_SCHEMA.TABLES to get only the tables. http://msdn.microsoft.com/en-us/library/ms188348.aspx

Option 1: How to loop?
Looping is done with a cursor
http://msdn.microsoft.com/en-us/library/ms180169.aspx

In the cursor you must construct a dynamic sql for the update and execute it
   DECLARE @cmd as varchar(8000)
   SET @cmd = 'UPDATE ' + @tablename + ' set ' + @colname +
                        ' = replace('+ @colname + ' , '','' , '';'' )'
   exec (@cmd)


Option 2: (better)
Construct the update statement in a select, execute it, verify output, copy output and execute the statements.
You can also first test it by not constructing an update but a select to see all the context you think to change.


select 'update '  + c.table_name + ' set ' + c.column_name +
                        ' = replace('+ c.column_name + ' , '','' , '';'' ) ' +
                        ' where ' + c.column_name +' like ''%,%'' '
from information_schema.columns as c
inner join information_schema.tables as t on t.table_name = c.table_name and t.table_schema = c.table_schema
where t.table_type ='BASE TABLE'

you just want to select the data?

have you considered the ms_foreachtable procedure

Actually, I wrote the pseudocode incorrectly.

It should be  update t set colname = replace (colname, ',',';') WHERE coname LIKE '%,%'

I didn,t know about the ms_foreachtable procedure. I'll have to check that out.

That update like is already in my comment.  
ms_foreachtable is ok when for each table, but now its for each column.

Mind my example there should be extra filtering on string-types.

Jogos,

Is there a way to use option 2
select 'update '  + c.table_name + ' set ' + c.column_name +
                        ' = replace('+ c.column_name + ' , '','' , '';'' ) ' +
                        ' where ' + c.column_name +' like ''%,%'' '
from information_schema.columns as c
inner join information_schema.tables as t on t.table_name = c.table_name and t.table_schema = c.table_schema
where t.table_type ='BASE TABLE'

so that the procedure does not just setup the update statements but actually runs them as well.

It's a one time operation, isn't it?
- safer to first check what is generated
- one time copy output to execute is much quicker as trying to make a way to automate that

Jogos,

This is likely to be run on a regular basis. Data is collected in the field by largely untrained people, Since we are creating .csv exports from the database, commas in the data are a problem.