SQL 如何在 MsSQL 中进行简单的“查找和替换”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/59044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I do a simple 'Find and Replace" in MsSQL?
提问by Jiaaro
Question is pretty self explanitory. I want to do a simple find and replace, like you would in a text editor on the data in a column of my database (which is MsSQL on MS Windows server 2003)
问题是不言自明的。我想做一个简单的查找和替换，就像在我的数据库列中的数据的文本编辑器中所做的一样（这是 MS Windows server 2003 上的 MsSQL）
回答by SQLMenace
The following query replace each and every acharacter with a bcharacter.
以下查询将每个a字符替换为一个b字符。
UPDATE
YourTable
SET
Column1 = REPLACE(Column1,'a','b')
WHERE
Column1 LIKE '%a%'
This will not work on SQL server 2003.
这不适用于 SQL Server 2003。
回答by Jiaaro
like so:
像这样：
BEGIN TRANSACTION;
UPDATE table_name
SET column_name=REPLACE(column_name,'text_to_find','replace_with_this');
COMMIT TRANSACTION;
Example: Replaces <script... with <a ... to eliminate javascript vulnerabilities
示例：将 <script... 替换为 <a ... 以消除 javascript 漏洞
BEGIN TRANSACTION; UPDATE testdb
SET title=REPLACE(title,'script','a'); COMMIT TRANSACTION;
回答by Brian Moeskau
This pointed me in the right direction, but I have a DB that originated in MSSQL 2000 and is still using the ntextdata type for the column I was replacing on. When you try to run REPLACE on that type you get this error:
这为我指明了正确的方向，但我有一个源自 MSSQL 2000 的数据库，并且仍在使用ntext我要替换的列的数据类型。当您尝试在该类型上运行 REPLACE 时，您会收到此错误：
Argument data type ntext is invalid for argument 1 of replace function.
参数数据类型 ntext 对于替换函数的参数 1 无效。
The simplest fix, if your column data fits within nvarchar, is to cast the column during replace. Borrowing the code from the accepted answer:
如果您的列数据适合nvarchar，最简单的解决方法是在替换期间强制转换列。从接受的答案中借用代码：
UPDATE YourTable
SET Column1 = REPLACE(cast(Column1 as nvarchar(max)),'a','b')
WHERE Column1 LIKE '%a%'
This worked perfectly for me. Thanks to this forum postI found for the fix. Hopefully this helps someone else!
这对我来说非常有效。感谢这个论坛帖子我找到了修复。希望这对其他人有帮助！
回答by abc123
The following will find and replace a string in every database (excluding system databases) on every table on the instance you are connected to:
以下将在您连接到的实例上的每个表的每个数据库（不包括系统数据库）中查找并替换一个字符串：
Simply change 'Search String'to whatever you seek and 'Replace String'with whatever you want to replace it with.
只需更改'Search String'为您想要的任何内容以及'Replace String'您想要替换的任何内容。
--Getting all the databases and making a cursor
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
DECLARE @databaseName nvarchar(1000)
--opening the cursor to move over the databases in this instance
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databaseName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @databaseName
--Setting up temp table for the results of our search
DECLARE @Results TABLE(TableName nvarchar(370), RealColumnName nvarchar(370), ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @SearchStr nvarchar(100), @ReplaceStr nvarchar(100), @SearchStr2 nvarchar(110)
SET @SearchStr = 'Search String'
SET @ReplaceStr = 'Replace String'
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
SET @TableName = ''
--Looping over all the tables in the database
WHILE @TableName IS NOT NULL
BEGIN
DECLARE @SQL nvarchar(2000)
SET @ColumnName = ''
DECLARE @result NVARCHAR(256)
SET @SQL = 'USE ' + @databaseName + '
SELECT @result = MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME))
FROM [' + @databaseName + '].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_CATALOG = ''' + @databaseName + '''
AND QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) > ''' + @TableName + '''
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)
), ''IsMSShipped''
) = 0'
EXEC master..sp_executesql @SQL, N'@result nvarchar(256) out', @result out
SET @TableName = @result
PRINT @TableName
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
DECLARE @ColumnResult NVARCHAR(256)
SET @SQL = '
SELECT @ColumnResult = MIN(QUOTENAME(COLUMN_NAME))
FROM [' + @databaseName + '].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''[' + @databaseName + '].' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''[' + @databaseName + '].' + @TableName + ''', 1)
AND DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
AND TABLE_CATALOG = ''' + @databaseName + '''
AND QUOTENAME(COLUMN_NAME) > ''' + @ColumnName + ''''
PRINT @SQL
EXEC master..sp_executesql @SQL, N'@ColumnResult nvarchar(256) out', @ColumnResult out
SET @ColumnName = @ColumnResult
PRINT @ColumnName
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'USE ' + @databaseName + '
SELECT ''' + @TableName + ''',''' + @ColumnName + ''',''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
--Declaring another temporary table
DECLARE @time_to_update TABLE(TableName nvarchar(370), RealColumnName nvarchar(370))
INSERT INTO @time_to_update
SELECT TableName, RealColumnName FROM @Results GROUP BY TableName, RealColumnName
DECLARE @MyCursor CURSOR;
BEGIN
DECLARE @t nvarchar(370)
DECLARE @c nvarchar(370)
--Looping over the search results
SET @MyCursor = CURSOR FOR
SELECT TableName, RealColumnName FROM @time_to_update GROUP BY TableName, RealColumnName
--Getting my variables from the first item
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @t, @c
WHILE @@FETCH_STATUS = 0
BEGIN
-- Updating the old values with the new value
DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = '
USE ' + @databaseName + '
UPDATE [' + @databaseName + '].' + @t + ' SET ' + @c + ' = REPLACE(' + @c + ', ''' + @SearchStr + ''', ''' + @ReplaceStr + ''')
WHERE ' + @c + ' LIKE ''' + @SearchStr2 + ''''
PRINT @sqlCommand
BEGIN TRY
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
--Getting next row values
FETCH NEXT FROM @MyCursor
INTO @t, @c
END;
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;
DELETE FROM @time_to_update
DELETE FROM @Results
FETCH NEXT FROM db_cursor INTO @databaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor
Note: this isn't ideal, nor is it optimized
注意：这不是理想的，也不是优化的
回答by Joe Kuemerle
If you are working with SQL Server 2005 or later there is also a CLR library available at http://www.sqlsharp.com/that provides .NET implementations of string and RegEx functions which, depending on your volume and type of data may be easier to use and in some cases the .NET string manipulation functions can be more efficient than T-SQL ones.
如果您使用的是 SQL Server 2005 或更高版本，那么http://www.sqlsharp.com/上还有一个 CLR 库，它提供字符串和 RegEx 函数的 .NET 实现，这取决于您的数据量和类型更容易使用，在某些情况下，.NET 字符串操作函数比 T-SQL 函数更有效。
