Dynamic SQL STUFF statement syntax

1

I'm trying to write a function that will take a table name and LTRIM(RTRIM( all the columns and replace all multiple spaces with singles.

I have the code that trim's all columns in a table, and I have the line that replaces all multiple spaces, but I can't seem to combine them without getting STUFF errors:

This works great and trims all text fields:

DECLARE @SQL AS VARCHAR(MAX),@schema_Table_name VARCHAR(MAX) = 'dbo.TestTable'

SET @SQL=STUFF((SELECT ', ' + QUOTENAME([name])

  • ' = (LTRIM(RTRIM(' + QUOTENAME([name]) + '))' FROM
    sys.columns WHERE object_id=object_id(@schema_Table_name)
    AND collation_name IS NOT NULL
    FOR XML PATH('')),1,1,'')
    PRINT @SQL

SET @SQL = 'UPDATE ' + @schema_Table_name + ' SET' + @SQL
PRINT @SQL

And this works great to replace all multiple spaces with a single space:

declare @SpaceTest varchar(1000) = '01 2 3 4 5 6 7 8 9 10 AbC D e F G h i '
SELECT replace(replace(replace(LTRIM(RTRIM(@SpaceTest)), ' ',' ' + char(7)), char(7) + ' ',''),char(7),'')

But when I try to combine the two I get an error:

DECLARE @SQL AS VARCHAR(MAX),@schema_Table_name VARCHAR(MAX) = 'dbo.TestTable'

SET @SQL=STUFF((SELECT ', ' + QUOTENAME([name])

  • ' = replace(replace(replace((LTRIM(RTRIM(' + QUOTENAME([name]) + ')), ' ',' ' + char(7)), char(7) + ' ',''),char(7),'')' FROM
    sys.columns WHERE object_id=object_id(@schema_Table_name)
    AND collation_name IS NOT NULL
    FOR XML PATH('')),1,1,'')
    PRINT @SQL

SET @SQL = 'UPDATE ' + @schema_Table_name + ' SET' + @SQL
PRINT @SQL

Error: Incorrect syntax near ' + char(7)), char(7) + '.
The intellisense error is: The STUFF function requires 4 arguments

2

Try this. It's dynamic sql , so for this you should add some additional '

DECLARE 
    @SQL AS VARCHAR(MAX)
    ,@schema_Table_name VARCHAR(MAX) = 'dbo.contact'

SET @SQL=STUFF((SELECT ', ' + QUOTENAME([name])
+ ' = replace(replace(replace(LTRIM(RTRIM(' + QUOTENAME([name]) + ')), '' '','' '' + char(7)), char(7) + '' '',''''),char(7),'''')' 
FROM
    sys.columns 
WHERE 
    object_id=object_id(@schema_Table_name)
    AND collation_name IS NOT NULL
FOR XML PATH('')),1,1,'')
PRINT @SQL

SET @SQL = 'UPDATE ' + @schema_Table_name + ' SET' + @SQL
PRINT @SQL
1 Like
3

Yup, thanks for the heads up that's exactly what it was.