sql - Is there a way to replace a character or string in all fields without writing it for each field? -

i warn front, question borders on silly, i'm asking anyway.

the impetus question creating csv query result , of fields containing commas already. obviously, csv doesn't know better , merrily jacks mood having stragglers in non-field columns.

i know can write

replace(fieldname, oldchar, newchar)

for each field, i'm more curious if there's shortcut replace them in query output.

basically i'm looking (logically) is:

replace(allfields, oldchar, newchar) 

i don't know of sql tricks (or many of them), thought maybe community may able enlighten me...or call me nuts.

there no sql syntax describe, you've seen there many ways dynamic sql. here's way prefer (this assumes want replace commas pipe, change see fit):

declare @table   nvarchar(511),         @newchar nchar(1),         @sql     nvarchar(max);  select @table    = n'dbo.table_name',        @newchar  = n'|', -- tailor accordingly        @sql      = n'';  select @sql = @sql + ',   ' + quotename(name)      + ' = replace(convert(nvarchar(max), ' + quotename(name) + '),'','','''      + @newchar + ''')'   sys.columns   [object_id] = object_id(@table)   order column_id;  select @sql = n'select ' + stuff(@sql, 1, 1, '')  + '   ' + @table;  print @sql; -- exec sp_executesql @sql;