Posted by Roy Harvey on 02/23/07 01:52

I don't see why you would have to update each column in an individual
query. Why not SET all the columns in one UPDATE? The code below
would simplify that. @tablename is used rather than a hardcoded value
to facilitate turning it into a stored procedure.

declare @tblname nvarchar(60)
set @tblname = 'PROFILE'

SELECT CASE WHEN C.colid = 1
THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
' SET '
ELSE ' '
END +
C.name + '= replace(cast(' + C.name +
' as nvarchar(255)),char(13)+char(10),'':'')' +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE ';'
END
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @tblname
ORDER BY C.id, C.colid

Output from one test exeuction:

UPDATE HoldEventsTable
SET TelephoneCallID= replace(cast(TelephoneCallID as
nvarchar(255)),char(13)+char(10),':'),
Time= replace(cast(Time as
nvarchar(255)),char(13)+char(10),':'),
Event= replace(cast(Event as
nvarchar(255)),char(13)+char(10),':');

Roy Harvey
Beacon Falls, CT

On 22 Feb 2007 16:01:40 -0800, "JackpipE" <pipe.jack@gmail.com> wrote:

>> 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.
>
>
>Well that simplify my job but still does not automate the process to a
>point where one query execution will take care of entire table.
>
>Thanks.

[Back to original message]