Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

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

I need to create a stored T-SQL  procedure (sql server 2005) that will do the following (pseudocode)

FOR EACH TABLE t
     FOR EACH column of type nvarchar (colname)
           SELECT REPLACE (colname, ',' , ';')  FROM t WHERE colname LIKE '%,%'
         
0
dblankman
Asked:
dblankman
  • 4
  • 3
1 Solution
 
jogosCommented:
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'

0
 
LowfatspreadCommented:
you just want to select the data?

have you considered the ms_foreachtable procedure
0
 
dblankmanAuthor Commented:
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
jogosCommented:
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.
0
 
dblankmanAuthor Commented:
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.
0
 
jogosCommented:
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
0
 
dblankmanAuthor Commented:
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.
0
 
jogosCommented:
Sounds like option 1 then.

Or better:
- see that imput is correct, so basicly set the replace in the input-app
- you can also set the replace in the statement that collects the data for export

In any way, a replace for all (string)fields in a database is something I don't want in my database. But if you're sure it's your call.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now