SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Replace on All Tables - Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

buck1107
Starting Member

6 Posts

Posted - 09/13/2012 :  17:25:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 09/13/2012 :  17:34:56  Show Profile  Reply with Quote
You can't use wildcards in the REPLACE function.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/13/2012 :  17:40:20  Show Profile  Reply with Quote
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/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4346 Posts

Posted - 09/13/2012 :  17:42:00  Show Profile  Reply with Quote
How would you get the LEN from a pattern with wildcards?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/13/2012 :  17:56:30  Show Profile  Reply with Quote
if patterns start and end is fixed you can still get it

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

Go to Top of Page

buck1107
Starting Member

6 Posts

Posted - 09/24/2012 :  16:01:22  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/24/2012 :  16:42:53  Show Profile  Reply with Quote
sorry i didnt get you.
can you illustrate with some sample data?

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

Go to Top of Page

buck1107
Starting Member

6 Posts

Posted - 09/24/2012 :  18:21:26  Show Profile  Reply with Quote
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.
Go to Top of Page

buck1107
Starting Member

6 Posts

Posted - 09/25/2012 :  13:31:42  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000