Replace on All Tables - Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replace on All Tables - Stored Procedure

Author

Topic

buck1107
Starting Member

6 Posts

Posted - 2012-09-13 : 17:25:31

Good Afternoon,
I am working on a Stored Procedure to replace a string in all tables. When I run a Select statement, I get matches with my search string; however, when I run the Update statement, none of the aforementioned matches are replaced.
Here is the search string: ">%http%<!--
(the opening double quote is part of the string, and the % are wildcards).

Any help or suggestions is greatly appreciated.
Thanks!

Below is my code for the Stored Procedure I'm using:


USE [cop]
GO
/****** Object:  StoredProcedure [dbo].[SearchAllTablesWildcard_Replace2]    Script Date: 09/13/2012 15:32:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesWildcard_Replace2]
(
@SearchStr nvarchar(100)
)
AS
BEGIN



SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = '%' + @SearchStr + '%'


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
            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
        )


         
        IF @ColumnName IS NOT NULL        
        BEGIN
           SET @SQL = 'UPDATE ' + @TableName + ' SET ' + 
                   @ColumnName + '= 
                   REPLACE(' + @ColumnName + ',''' + @SearchStr2 + ''','+''''')' +
                   ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + ''''
           --PRINT(@SQL )
           EXEC(@SQL)
        END 
        
        

    
        
    END 
END



 END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-13 : 17:34:56

You can't use wildcards in the REPLACE function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 17:40:20

one way to do is using STUFF and PATINDEX

STUFF(yourcolumn,PATINDEX('your pattern here',youcolumn),LEN('your pattern'),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-13 : 17:42:00

How would you get the LEN from a pattern with wildcards?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 17:56:30

if patterns start and end is fixed you can still get it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

buck1107
Starting Member

6 Posts

Posted - 2012-09-24 : 16:01:22

quote:
Originally posted by visakh16

if patterns start and end is fixed you can still get it

Can the patten in PATINDEX actually open without a wildcard character, so that the start and end are fixed? I've tried

PATINDEX('">%http%<!--', AcademicDishonestyDesc)

but it only finds matching patterns, if there is nothing preceding or following the pattern in the string.
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-24 : 16:42:53

sorry i didnt get you.
can you illustrate with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

buck1107
Starting Member

6 Posts

Posted - 2012-09-24 : 18:21:26

quote:
Originally posted by visakh16

sorry i didnt get you.
can you illustrate with some sample data?

Thanks,
If I want to look for a string that always begins with and ends with the same characters, and if there is the potential for more data before or after the search pattern, how would it be possible to search for the 'fixed' beginning and ending, e.g.

sample: ">corvettes<http://www.corvette.com>corvettes<!--
sample search: ">%http%<!--

The double quote is a part of the search string.

Thanks for your help.

buck1107
Starting Member

6 Posts

Posted - 2012-09-25 : 13:31:42

quote:
Originally posted by visakh16

if patterns start and end is fixed you can still get it

Maybe I can clarify my question further: how do I get the patterns start and end is fixed, as stated above?

Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources