CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AzaRoth91
AzaRoth91

Reputation: 283

SELECT REPLACE on every column

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(*, '"', '')

Upvotes: 3

Views: 437

Answers (4)

Ernesto Lugo
Ernesto Lugo

Reputation: 59

another simple way.

declare @sql varchar(max) = '',
        @Tname varchar(30) = 'table',
        @stringtolook varchar(10) = '"""',
        @stringtoreplace varchar(10) = ''

select @sql = @sql + 'update '+@Tname+' set [' + c.name + '] = replace(' + c.name + ', ''' + @stringtolook + ''', ''' + @stringtoreplace + ''') '
    from sys.columns c
    inner join sys.tables t on c.object_id = t.object_id
    where t.name = @Tname

exec (@sql)

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

Here is a recursive version:

declare @TABLE_NAME sysname = 'MyTableName'
declare @Prefix nvarchar(128) = 'REPLACE('
declare @Suffix nvarchar(128) = ', ''"'', '''')'
declare @Sql nvarchar(max)

;with Cols (TABLE_NAME, SELECT_LIST, ITERATION) as
(
    select TABLE_NAME
        , cast('' as nvarchar(max)) as SELECT_LIST
        , 0 as ITERATION
    from INFORMATION_SCHEMA.TABLES
    where TABLE_NAME = @TABLE_NAME
    union all
    select c.TABLE_NAME
        , c.SELECT_LIST
            + case when len(c.SELECT_LIST) > 0 then ', ' else '' end
            + case when i.DATA_TYPE like '%char' then @Prefix else '' end
            + cast(i.COLUMN_NAME as nvarchar(128))
            + case when i.DATA_TYPE like '%char' then @Suffix + ' as ' + cast(i.COLUMN_NAME as nvarchar(128)) else '' end
        , c.ITERATION + 1
    from INFORMATION_SCHEMA.COLUMNS i
        join Cols c on i.TABLE_NAME = c.TABLE_NAME
    where i.ORDINAL_POSITION = c.ITERATION + 1
)
select @Sql = ('select ' + a.SELECT_LIST + ' from ' + a.TABLE_NAME)
from Cols a
    join (
        select TABLE_NAME, max(ITERATION) as ITERATION
        from Cols
        group by TABLE_NAME
    ) as b on a.TABLE_NAME = b.TABLE_NAME
        and a.ITERATION = b.ITERATION

exec (@sql)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 4

Phil
Phil

Reputation: 42991

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

Upvotes: 1

Related Questions