Posted by JackpipE on 02/23/07 03:33

On Feb 22, 8:52 pm, Roy Harvey <roy_har...@snet.net> wrote:
> 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
>
Roy,

I had different output when I ran your query:
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':'),
_NAME= replace(cast(_NAME as nvarchar(255)),char(13)+char(10),':');

It looked like select statement output with 17 rows (17 columns in the
table) like the above. No UPDATE or SET function.

• Next in forum: Re: test
• Prev in forum: Re: Query for the first and latest wish
• Thread view: Re: How to run replace on all columns

[Reply to this message]