Solved

Help modifying search and replace stored proc

Posted on 2007-12-04
4
222 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

912 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now