CODEWITHSUNDEEP
sqlsql-server
Nic
Nic

Reputation: 1310

Modify columns using stored procedure in SQL Server

I wish to modify strings in several columns (for example all columns containing the 'sound' string), for example replacing ',' by '.'. Further to this post, I understand I have to use dynamic SQL. I created the following procedure: 

USE [myDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RemoveStringInColumn] (@colName varchar(50), @tableName varchar(50), @to_remove varchar(50), @to_add varchar(50))
AS

DECLARE @sql nvarchar(4000)
SET @sql = 'UPDATE ' + @tableName + ' SET ' + @colName + ' = REPLACE(' + @colName + ',' + @to_remove + ','+ @to_add + ');'
PRINT @sql
EXEC sp_executesql @sql

Which is called by:

EXEC dbo.RemoveStringInColumn 'COL_1', 'TABLE_1', ',', '.'

1) The problem is the @sql command does not contain the little hyphen arond the comma and the dot. How can I solve this?

2) In this post they use a SELECT command to fetch all column names. So far, I managed to fetch column names containing 'sound'. 

select COLUMN_NAME AS my_cols
from INFORMATION_SCHEMA.COLUMNS 
where table_name = 'TABLE_1' AND COLUMN_NAME LIKE '%sound%'

How can I put column names into a list and use a for loop to go through them calling the RemoveStringInColumn procedure?

Thanks

Upvotes: 2

Views: 658

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93714

Just double the single quotes around @to_remove and @to_add

DECLARE @sql NVARCHAR(4000)

SET @sql = 'UPDATE ' + Quotename(@tableName) + ' SET ' + Quotename(@colName)
           + ' = REPLACE(' + Quotename(@colName) + ',''' + @to_remove + ''','''
           + @to_add + ''');'

PRINT @sql

EXEC Sp_executesql
  @sql

Update : To do the replace for more than one column 

DECLARE @sql       NVARCHAR(4000),
        @col_list  VARCHAR(8000)= ''

SET @col_list = (SELECT ',' + Quotename(COLUMN_NAME) + ' = REPLACE('
                        + Quotename(COLUMN_NAME) + ',''' + @to_remove
                        + ''',''' + @to_add + ''')'
                 FROM   INFORMATION_SCHEMA.COLUMNS
                 WHERE  table_name = 'TABLE_1'
                        AND COLUMN_NAME LIKE '%sound%'
                 FOR xml path(''))
SET @col_list = Stuff(@col_list, 1, 1, '')

SELECT @col_list

SET @sql = 'UPDATE ' + Quotename(@tableName) + ' SET '
           + @col_list

PRINT @sql

EXEC Sp_executesql
  @sql

Upvotes: 3

Related Questions