Solved

Help modifying search and replace stored proc

Posted on 2007-12-04
4
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

635 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