Escaping Quotes - Stored Procedure - MS SQL 2008

Question

I am working with a Stored Procedure in MS SQL 2008.
The problem with my code lies somewhere in the UPDATE section. I am trying to replace a string that begins with a single double-quote.

Here is the string that I'm searching for, and I wish to replace it with nothing. Could this be a problem with escaping quotes, etc?

">%http%<!--

Many thanks. Below is the stored procedure.

USE [cop_pcms]
GO
/****** Object:  StoredProcedure [dbo].[SearchAllTablesWildcard_Replace2]    Script    Date: 09/11/2012 11:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SearchAllTablesWildcard_Replace2]
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @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
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)' +
                ' FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' like ' + @SearchStr2
            )
        END


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

    END 
END

SELECT ColumnName, ColumnValue FROM #Results
 END

What is the problem you are experiencing? An incorrect update, or a runtime error, or something else? Sorry to be dense, but the problem just wasn't evident to me. — David W, Sep 11 '12 at 17:22
Thank you. The problem is that the UPDATE doesn't seem to be working, as no replacement is occurring. Would the REPLACE in the UPDATE need to have four double quotes in the third parameter, rather than single quotes, to indicate 'nothing'? — buck1112, Sep 11 '12 at 17:39

John Sobolewski · Answer 1 · 2012-09-11T17:34:35.300

I'm having a hard time figuring out what your question is... but...

this should do what you want if you want to just replace a double quote

select REPLACE('testing"testing','"','')

Also this example should work if you want to replace the string ">%http%

select REPLACE('testing">%http%<!--testing','">%http%<!--','')

this however will not do anything

select REPLACE('testing">**http**<!--testing','">%http%<!--','')

To be more clear. this doesn't do it either... note: the % isn't a wildcard in the "pattern"

select REPLACE('1111a23333','1a%3','')

Also QUOTENAME(TABLE_NAME) will put square brackets around that which I think is what you think is happening?

So maybe this is your problem

REPLACE(' + @ColumnName + ',' + @SearchStr2 + ',' + ''''')'

This is replacing the thing... followed by two single quotes.

See: REPLACE (Transact-SQL)

Once I removed the two 'escaping' single quotes, I received the following error messages: Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string '>%http% — buck1112, Sep 11 '12 at 19:13

Escaping Quotes - Stored Procedure - MS SQL 2008

1 Answers1