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