Solved

Help modifying search and replace stored proc

Posted on 2007-12-04
4
225 Views
Last Modified: 2008-02-01
Hi everyone.
I need to modify this search and replace stored proc currently If I perform

exec searchandreplace 'tom', 'tommy'   it will find things like 'tomorrow' and make it 'tommyorrow'. What I need it to do is only do replacements for Full String replacement ("tom" must match "tom" exactly)

A work around to having to find the Full String replacement ("tom" must match "tom" exactly) could be a way to specify a specific column names to replace in.

But for this to work It would have to work for a range of column names as its not exactly the same in all tables.

Thanks



CREATE PROC SearchAndReplace
(
      @SearchStr nvarchar(100),
      @ReplaceStr nvarchar(100)
)
AS
BEGIN
 
      
 
 
      SET NOCOUNT ON
 
      DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL  
nvarchar(4000), @RCTR int
 
--- disable triggers
/*To disable triggers*/
DECLARE C CURSOR FOR
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' DISABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
OPEN C
FETCH NEXT FROM C INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
  EXEC( @SQL )
  FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C
 
--- original script begin
      SET  @TableName = ''
      SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
      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'
                        AND      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                        AND      OBJECTPROPERTY(
                                    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 C
                        WHERE             TABLE_SCHEMA      = PARSENAME(@TableName, 2)
                              AND      TABLE_NAME      = PARSENAME(@TableName, 1)
                              AND      DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                              AND      QUOTENAME(COLUMN_NAME) > @ColumnName
--- begin test for computed
                              AND NOT EXISTS (SELECT * FROM SYSCOLUMNS S
                                WHERE S.NAME = C.COLUMN_NAME
                                  AND S.ID = OBJECT_ID(C.TABLE_NAME)
                                  AND S.ISCOMPUTED = 1)
--- end test for computed
                  )
      
                  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
 
--- ENABLE TRIGGERS
 
DECLARE C CURSOR FOR
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' ENABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
OPEN C
FETCH NEXT FROM C INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
  EXEC( @SQL )
  FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C

Open in new window

0
Comment
Question by:tconnelly
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Otana earned 500 total points
ID: 20409428
If you want an exact match, take the '%' out of your string.
0
 

Author Comment

by:tconnelly
ID: 20409492
so make the line

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

like this ?

SET @SearchStr2 = QUOTENAME(@SearchStr,'''')
0
 
LVL 11

Expert Comment

by:Otana
ID: 20409515
Yes, try it and see what happens.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409681
line 114:       DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SQL  
line 36: -- delete this line
line 80:        ') WHERE ' + @ColumnName + ' = ' + @SearchStr

Hi tconnelly, this one is easy enough. The other aspects will be more tricky, but you'll get much more traction with others taking a task at a time. Have fun.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 26
Import New Records From Access Table To SQL Database Table 7 33
Parse this column 6 27
MS SQL GROUP BY 5 7
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question