在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:

我希望在几个列中修改字符串(例如包含'sound' string的所有列)，例如替换' by '。在本文后面，我理解我必须使用动态SQL。我创建了以下程序:

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?

1)问题是@sql命令不包含连字符、逗号和点。我怎么解决这个问题?

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

2)在本文中，他们使用SELECT命令获取所有列名。到目前为止，我成功地获取了包含“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?

如何将列名放入列表并使用for循环遍历它们调用RemoveStringInColumn过程?

Thanks

谢谢

1 个解决方案

#1

Just double the single quotes around @to_remove and @to_add

只需将@to_remove和@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

#1