SELECT REPLACE on every column

Question

I have a table containing around 100 columns, is it to possible to do a Select Replace on every column at the same time rather than typing out each column individually, i'm trying to trim the '"' of each field in the table.

SELECT
    REPLACE(*, '"', '')

Answer 1

DECLARE @tablename nvarchar(100)= 'Test'
DECLARE @col nvarchar(max)
SELECT @col = coalesce(@col + ',', 'select ' ) + 
case when data_type in ('varchar', 'char','nvarchar', 'nchar') then 
'replace('+column_name+' , ''"'', '''') '+' as [' + column_name + ']' else '[' + column_name + ']' end 
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE table_name = @tablename
SET @col += ' from ' +  @tablename

EXEC (@col)

Answer 2

Since you're using SQL Server, you can retrieve the names of all columns on a table using the INFORMATION_SCHEMA, e.g.

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'yourTable'

You can then use a cursor to iterate over each column name, build some dynamic SQL and execute this using 'exec sp_executesql'.

Here's my solution:

declare @isString bit
declare @tableName nvarchar(256) = N'MyTableName'
declare @columnName nvarchar(max)
declare @sql nvarchar(max) = ''

declare c cursor local forward_only read_only for 
    select column_name, case when CHARACTER_SET_NAME is null then 0 else 1 end as IsString
    from information_schema.COLUMNS WHERE table_name = @tableName

open c

fetch next from c into @columnName, @isString

set @sql = N'select '

declare @first bit = 1

while @@FETCH_STATUS = 0
begin
    select @columnName

    if @isString <> 0 
    begin
        if @first = 0
        begin
            set @sql = @sql + ', '
        end

        set @sql = @sql + N'REPLACE(' + @columnName + ', ''"'', '''')'
        set @first = 0
    end

    fetch next from c into @columnName, @isString
end

close c
deallocate c

set @sql = @sql + ' from ' + @tableName

exec sp_executesql @sql