SQL Server: How to replace carriage return characters

Question

I'm using the following procedure to replace all carriage return characters within all tables, with ' + ':

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
    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
                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 + ', ' 
                        + 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
GO

After creating the procedure, and calling it as:

EXEC SearchAndReplace 'GONNA', 'GOING'

It shows:

Outcome ---------------------------------------------------- Replaced 148 occurence(s)

And it's the intended result.

---

Problem:

1. First case:

   Calling EXEC SearchAndReplace CHAR(10), ' + ' results in the following result:

   Msg 102, Level 15, State 1, Server WIN-M6K7U0AQLHJ, Line 1 Incorrect syntax near '10'.

2. Second case:

   Calling EXEC SearchAndReplace 'CHAR(10)', ' + ' results in the following result:

   Outcome ---------------------------------------------------- Replaced 0 occurence(s)

3. Third case:

   Calling EXEC SearchAndReplace 'CHAR(13)', ' + ' results in the following result:

   Outcome ---------------------------------------------------- Replaced 0 occurence(s)

Which means the query failed to find the carriage return characters and replace them; Although I'm pretty sure, from the CSV export, there is unique cells exported as broken into many lines, due to the carriage return characters that do obviously exist within the database.

---

I'm new to SQL Server, and I can't know exactly what I'm doing wrong.

Any help will be greatly appreciated.

Answer 1

try to store Char(10) value in a variable and then pass that variable as a parameter.

declare @tmp varchar(10)=CHAR(10)
EXEC SearchAndReplace @tmp, ' + '