SQL loop over columns and excute REPLACE statement

Question

I am trying to replace a CRLF character in a table in SQL Server. The statement for one column works and goes like this:

select REPLACE(REPLACE(col_name,char(13),''), char(10), '') from table_name

Now I would like to repeat this for every column in my table. I have the following script which is not working:

Declare @sql varchar(max) = ''

select @sql = @sql + 'select [' + c.name + '] REPLACE(REPLACE(' + c.name + ', char(13),''), char(10), '') from [' + t.name + ']; ' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = table_name

EXEC (@sql)

Unfortunately this doesn't work and I get the following error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'REPLACE'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'REPLACE'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near 'REPLACE'.
Msg 102, Level 15, State 1

Just noticed, you use select, but if you want to get rid of that you need update. Add this to my answer. — gofr1
– gofr1, Commented Sep 30, 2016 at 12:54

Gavin Campbell · Accepted Answer · 2016-09-30 12:35:32Z

2

Two things; ~~as noted by the other answer~~ there needs to be a comma after the initial [c.name]. Also, to get a single quote inside the string, you need two single quotes. Hence, to get two single quotes together, you need four single quotes.

select @sql = @sql + 'select [' + c.name + '], REPLACE(REPLACE(' + c.name + ', char(13),''''), char(10), '''') from [' + t.name + ']; ' 
    from sys.columns c
    inner join sys.tables t on c.object_id = t.object_id
    where t.name = 'table_name'

    exec (@sql)

NB: print(@sql) is a great help in debugging such problems!

edited Sep 30, 2016 at 12:35

answered Sep 30, 2016 at 12:29

Gavin Campbell

8366 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Lange Over a year ago

You have inconsistent usage of square brackets. And to be honest you shouldn't use them in this type of situation. You should eliminate the brackets and instead use QUOTENAME. When you hard code brackets like this if there is a bracket in the name it will crash. And it important enough to use them once you should be consistent. Still this is a good answer and worth of a +1.

gofr1 · Accepted Answer · 2016-09-30 13:09:08Z

2

Add quotes, remove first c.name or add comma, instead of adding [] use QUOTENAME:

Declare @sql varchar(max) = ''

select @sql = @sql + 'select ' + QUOTENAME(c.name) + ', REPLACE(REPLACE(' + QUOTENAME(c.[name]) + ', char(13),''''), char(10), '''') from ' + QUOTENAME(t.[name]) + '; ' 
from sys.columns c
inner join sys.tables t 
    on c.object_id = t.object_id
where t.[name] = 'table_name'

EXEC (@sql)

EDIT

If you need to update change @sql part:

select @sql = @sql + 'UPDATE ' + QUOTENAME(t.[name]) + ' SET ' + QUOTENAME(c.name) + ' = REPLACE(REPLACE(' + QUOTENAME(c.[name]) + ', char(13),''''), char(10), '''') ; '

edited Sep 30, 2016 at 13:09

answered Sep 30, 2016 at 12:34

gofr1

16k11 gold badges47 silver badges56 bronze badges

Comments

Kannan Kandasamy · Accepted Answer · 2016-09-30 12:33:31Z

1

You can try this way, variable is not set with replaced value, quotes are not double-quoted...

Declare @sql varchar(max) = ''

select @sql = @sql + 'select [' + c.name + '] = REPLACE(REPLACE(' + c.name + ', char(13),''''), char(10), '''') from [' + t.name + ']; ' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = 'commasep'

select @sql

answered Sep 30, 2016 at 12:33

Kannan Kandasamy

14.1k3 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL loop over columns and excute REPLACE statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related