Trim all database fields

Question

Do you know if there's a quick way in sql server (via transact-sql) that I could trim all the database string fields.

Answer 1

Just make sure you are doing a trim on VARCHAR string fields, not CHAR fields :)

That wouldn't do much good.

Answer 2

Your question is a bit vague but is this what you are after?

UPDATE mytable SET mycolumn= LTRIM(RTRIM(mycolumn))

That will remove both leading and trailing spaces from all values in the 'mycolumn' column in the 'mytable' table.

Answer 3

If anyone knows how to do this without a cursor, please post it:

DECLARE @CRLF AS varchar(2)
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @TAB AS varchar(1)
SET @TAB = CHAR(9)

DECLARE @template AS varchar(max)
SET @template = 'UPDATE {@OBJECT_NAME}' + @CRLF + 'SET {@column_list}'

DECLARE c CURSOR FAST_FORWARD
    FOR SELECT  DISTINCT
                QUOTENAME(T.TABLE_CATALOG) + '.' + QUOTENAME(T.TABLE_SCHEMA)
                + '.' + QUOTENAME(T.TABLE_NAME) AS [OBJECT_NAME]
        FROM    INFORMATION_SCHEMA.TABLES AS T
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C
                ON T.TABLE_CATALOG = C.TABLE_CATALOG
                   AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
                   AND T.TABLE_NAME = C.TABLE_NAME
                   AND T.TABLE_TYPE = 'BASE TABLE'
                   AND C.DATA_TYPE IN ('varchar', 'nvarchar')
        ORDER BY 1

DECLARE @OBJECT_NAME AS sysname

OPEN c

FETCH NEXT FROM c INTO @OBJECT_NAME
WHILE @@FETCH_STATUS = 0
    BEGIN 
        DECLARE @column_list AS varchar(max)
        SELECT  @column_list = COALESCE(@column_list + @CRLF + @TAB + ',', '')
                + QUOTENAME(C.COLUMN_NAME) + ' = LTRIM(RTRIM('
                + QUOTENAME(C.COLUMN_NAME) + '))'
        FROM    INFORMATION_SCHEMA.COLUMNS AS C
        WHERE   C.DATA_TYPE IN ('varchar', 'nvarchar')
                AND QUOTENAME(C.TABLE_CATALOG) + '.'
                + QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) = @OBJECT_NAME
        ORDER BY C.ORDINAL_POSITION

        PRINT REPLACE(REPLACE(@template, '{@column_list}', @column_list),
                      '{@OBJECT_NAME}', @OBJECT_NAME)

        FETCH NEXT FROM c INTO @OBJECT_NAME
    END

CLOSE c

DEALLOCATE c