November 12, 2015 at 1:40 pm
Hi,
I am currently trying to find a way to search/replace certain character strings across an entire database. I found the script (below), which seems to do a good job with exact words, but doesn't seem to work well with wildcards in the string.
For example, I want to look for:
<title>text</title>more text
and replace everything from <title> to the end of the string with 'nothing' '', I've tried modifying the code below with set values for @SearchStr and @Replacestr.
I've also wanted to alter the code to just search in one table but wasn't sure how to do it here. Additionally a print of the lines (and their tables) which get replaced would be desirable.
Am I on the right line here, or what might make this code work better to suit my needs here?
Thanks for any helpful input or leads. 🙂
note: I tried to contact the author of this great script, but it seems his site contact info is no longer functioning.
Kind Regards
USE [cop]
GO
/****** Object: StoredProcedure [dbo].[SearchAllTablesAndReplace_4] Script Date: 11/12/2015 2:31:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesAndReplace_4]
--(
--@SearchStr nvarchar(100)
--,
--@ReplaceStr nvarchar(100)
--)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @sql nvarchar(4000), @RCTR int ,@SearchStr nvarchar(110), @ReplaceStr nvarchar(110)
SET @TableName = ''
SET @SearchStr = '%</title>%'
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @ReplaceStr = ''
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @sql='UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
GO
November 13, 2015 at 12:19 am
To debug replace
EXEC (@SQL)
with
PRINT @sql
You will see the SQL it generates.
You can copy a line back into a SQL Server Management Studio query window, and run it (or see whether SSMS' parser detects a syntax error).
Next, refer to String Functions (Transact-SQL) and Wildcards in Transact-SQL. Your rewritten SET @sql = string expression should probably use LIKE, %, LEFT, and CHARINDEX. Remember to escape apostrophes within the @sql string, by doubling them.
It would be best to not[/I] use QUOTENAME the way the author is using it with @SearchStr and @ReplaceStr:crazy:. QUOTENAME returns NVARCHAR(258), because it is designed to handle schema-qualified object names (==not all strings). You can expect QUOTENAME to fail (truncate results), when its string argument is greater than 258 characters. A more robust solution is to simply use escaped apostrophes within @sql's string, on either side of concatenations with @SearchStr and @ReplaceStr.
November 17, 2015 at 5:19 am
You can also use "SELECT @sql" which will do the same thing as PRINT but just stick it in the RESULTS tab instead of the MESSAGES tab.
November 24, 2015 at 11:21 am
Thanks,
I've been working on this search/replace string and had a question about why the replace still is not occurring.
Below is the actual SQL I've been using in test:
UPDATE [dbo].[tbl_a]
SET [a_col] = REPLACE([a_col],'</title>%','')
WHERE [a_col] LIKE '%</title>%'
When used in a SELECT statement, the 'LIKE' clause, as above, does find the occurrences of the '%</title>%' string; however, no replace occurs.
There is a message, however, stating that x number of rows was affected.
I would like for all text, beginning with "</title>," through the end of the string, to be replaced with nothing ('').
Are my escaped characters in need of editing?
Thanks 🙂
November 24, 2015 at 11:26 am
Your escape characters need escaping. @=)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply