CODEWITHSUNDEEP
sqlsql-server
Pr0no
Pr0no

Reputation: 4099

Perform replace on all columns of a select statement

Assume the following #temptable

ID    location    price    quantity    comment
----------------------------------------------
1     new york      15        10       nice

In reality, the table has many more columns. I want to quickly make a select and perform a replace operation on each column:

select
    replace(ID, char(10), ' ')
  , replace(location, char(10), ' ')
  , replace(price, char(10), ' ')
  , replace(quantity, char(10), ' ')
  , replace(comment, char(10), ' ')
from
    #temptable
;

To do this on 1000 columns, for multiple tables, is cumbersome. So I am trying to do this dynamically:

declare @sql nvarchar(max)
declare @column varchar(max)

declare cur cursor for select * 
                       from tempdb.sys.columns 
                       where object_id = object_id('tempdb..#temptable')

fetch next from cur into @column
while @@fetch_status = 0
begin   
    set @sql = @sql + 'replace(' + @column + ', char(10), ""),'
    fetch next from cur;
end

@sql = 'select ' + @sql + ' from #temptable'

select @sql 

--sp_execute(@sql)

This is the best I can come up with, but obviously it does not work. Can you help me getting this code to work?

Upvotes: 1

Views: 2357

Answers (2)

jpw
jpw

Reputation: 44881

Instead of using a cursor to build the statement to execute you can do it this way:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @cols AS NVARCHAR(MAX)

SELECT @cols = ISNULL(@cols + CHAR(10) + ',','') 
               + 'REPLACE(' + QUOTENAME(name) + ', CHAR(10), '' '')' 
FROM tempdb.sys.columns WHERE object_id = object_id('tempdb..#temptable')

SET @sql = N'SELECT ' + @cols  + ' FROM #temptable'

EXEC sp_executesql @sql

Given your sample temp table this would build and execute the following statement:

SELECT REPLACE([ID], CHAR(10), ' ')
,REPLACE([location], CHAR(10), ' ')
,REPLACE([price], CHAR(10), ' ')
,REPLACE([quantity], CHAR(10), ' ')
,REPLACE([comment], CHAR(10), ' ') FROM #temptable

You should probably qualify the query from sys.columns and add a filter for system_type_id to only operate on columns with a character data type (like 167 (varchar) or 175 (char)), if it's not meaningful for you to run the replace on binary data too.

Upvotes: 3

Joe C
Joe C

Reputation: 3993

Note open cursor, close cursor and deallocate cursor. Also you want to select the name column for your cursor. Good article on the topic - https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/ 

declare @sql nvarchar(max) = 'Select '
declare @column varchar(8000)

declare cur cursor for select name 
                       from tempdb.sys.columns 
                       where object_id = object_id('tempdb..#temptable')
Open cur

fetch next from cur into @column;
while @@fetch_status = 0
begin   
    set @sql = @sql + 'replace(' + @column + ', char(10), '' ''),'
    --select @sql
    fetch next from cur into @column;
end
close cur
deallocate cur

Set @sql = Substring(@sql,1,Len(@sql)-1) + ' from #temptable'

--select @sql

exec (@sql)

Upvotes: 2

Related Questions