Posted by Erland Sommarskog on 02/22/07 21:41

JackpipE (pipe.jack@gmail.com) writes:
> Here is my replace query and I need to run this on every column in my
> table. Right now I manually enter the column name (_LANGUAGES_SPOKEN)
> but this is time consuming and would like to automate this process as
> much as possible.
>
> Update PROFILE
> SET LANGUAGES_SPOKEN = replace(cast(_LANGUAGES_SPOKEN as
> nvarchar(255)),char(13)+char(10),':')

There is no way to loop through the columns in a table in a simple
fashion. This is because that it would rarely make any sense; columns
in a table are supposed to described distinct attribuets.

For a thing like this I would do:

SELECT 'UPDATE PROFILE SET ' + name + ' replace(substring( ' +
name + ', 1, 255), char(13) + char(10), '':'')'
FROM syscolumns
WHERE id = object_id('PROFILE')
and type_name(xtype) like '%char'

and the copy, paste and run result.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]