Trim all database fields

Question

Hello,

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

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 2

loop over information_schema.columns and RTRIM the varchar/nvarchar columns by creating the update statement dynamically

Answer 3

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

That wouldn't do much good.

Answer 4

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

Answer 5

No cursors. Copy and paste the output. Works also for SQL 2000, which doesn't have varchar(max). This can be easily extended to add a GO line to the end of each UPDATE if desired.

SELECT  SQL
FROM    (       SELECT  t.TABLE_CATALOG
                 ,      t.TABLE_SCHEMA
                 ,      t.TABLE_NAME
                 ,      0               SORT
                 ,      'UPDATE  ' + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)       SQL
                FROM    INFORMATION_SCHEMA.TABLES       t
                 JOIN   INFORMATION_SCHEMA.COLUMNS      c
                  ON    t.TABLE_CATALOG = c.TABLE_CATALOG
                   AND  t.TABLE_SCHEMA  = c.TABLE_SCHEMA
                   AND  t.TABLE_NAME    = c.TABLE_NAME
                WHERE   t.TABLE_TYPE    = 'BASE TABLE'
                 AND    c.DATA_TYPE     IN ('char','nchar','varchar','nvarchar')
                GROUP BY        t.TABLE_CATALOG
                 ,              t.TABLE_SCHEMA
                 ,              t.TABLE_NAME
                UNION ALL
                SELECT  x.TABLE_CATALOG
                 ,      x.TABLE_SCHEMA
                 ,      x.TABLE_NAME
                 ,      CASE    WHEN    x.COLUMN_NAME_MIN       = y.COLUMN_NAME
                                THEN    1
                                ELSE    2
                        END                                                     SORT
                 ,      CASE    WHEN    x.COLUMN_NAME_MIN       = y.COLUMN_NAME
                                THEN    'SET     '
                                ELSE    ' ,      '
                        END + y.SQL                                             SQL
                FROM    (       SELECT  t.TABLE_CATALOG
                                 ,      t.TABLE_SCHEMA
                                 ,      t.TABLE_NAME
                                 ,      MIN(c.COLUMN_NAME)      COLUMN_NAME_MIN
                                FROM    INFORMATION_SCHEMA.TABLES       t
                                 JOIN   INFORMATION_SCHEMA.COLUMNS      c
                                  ON    t.TABLE_CATALOG = c.TABLE_CATALOG
                                   AND  t.TABLE_SCHEMA  = c.TABLE_SCHEMA
                                   AND  t.TABLE_NAME    = c.TABLE_NAME
                                WHERE   t.TABLE_TYPE    = 'BASE TABLE'
                                 AND    c.DATA_TYPE     IN ('char','nchar','varchar','nvarchar')
                                GROUP BY        t.TABLE_CATALOG
                                 ,              t.TABLE_SCHEMA
                                 ,              t.TABLE_NAME
                        )       x
                 JOIN   (       SELECT  t.TABLE_CATALOG
                                 ,      t.TABLE_SCHEMA
                                 ,      t.TABLE_NAME
                                 ,      c.COLUMN_NAME
                                 ,      QUOTENAME(c.COLUMN_NAME) + ' = LTRIM(RTRIM(' + QUOTENAME(c.COLUMN_NAME) + '))'  SQL
                                FROM    INFORMATION_SCHEMA.TABLES       t
                                 JOIN   INFORMATION_SCHEMA.COLUMNS      c
                                  ON    t.TABLE_CATALOG = c.TABLE_CATALOG
                                   AND  t.TABLE_SCHEMA  = c.TABLE_SCHEMA
                                   AND  t.TABLE_NAME    = c.TABLE_NAME
                                WHERE   t.TABLE_TYPE    = 'BASE TABLE'
                                 AND    c.DATA_TYPE     IN ('char','nchar','varchar','nvarchar')
                        )       y
                  ON    x.TABLE_CATALOG = y.TABLE_CATALOG
                   AND  x.TABLE_SCHEMA  = y.TABLE_SCHEMA
                   AND  x.TABLE_NAME    = y.TABLE_NAME
        )       x
ORDER BY        x.TABLE_CATALOG
 ,              x.TABLE_SCHEMA
 ,              x.TABLE_NAME
 ,              x.SORT
 ,              x.SQL

Answer 6

Thanks guys,

Entaroadun code worked pretty well for me, I just had to do some small changes to my requierements, and also I had to reset @colum_list on each iteration.

...
  PRINT REPLACE(REPLACE(@template, '{@column_list}', @column_list),
                          '{@OBJECT_NAME}', @OBJECT_NAME)
    PRINT 'GO'
    SELECT  @column_list = null
            FETCH NEXT FROM c INTO @OBJECT_NAME
...

Answer 7

-- V Quick and Dirty !

-- If this is to generate code to run on a single table, run this code with text output ;

set nocount on

declare @table nvarchar(100) select @table = 'YourTableHere'

SELECT 'UPDATE ' + @table + ' SET ' SELECT '[' + Column_Name + '] = ' + 'LTRIM(RTRIM([' + Column_Name + '])), ' FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = @table AND Data_Type LIKE '%CHAR%'

-- Run as text output (Query/Results To... Results To Text -- Copy and paste the text output (excluding the last comma) into a new query window, and run it .

Answer 8

OK, that was quick and dirty but i have been sufficiently motivated by a current project to do this 'properly' - and with no cursors either, but a little sql concatenation trick. Does use dynamic sql though

-- exec spGenerateTrimStatements 'StaticImportMaturities' ALTER PROCEDURE spGenerateTrimStatements ( @TableName NVARCHAR(100) ) AS DECLARE @Cr char(2),
@OutputString nvarchar(max)

    SELECT  @Cr = CHAR(13) + CHAR(10)
    SET     NOCOUNT ON

    -- Create table to store commands
    CREATE  TABLE   #tOutput(OutputText nvarchar(500), RowID int identity(1,1))

    -- Build up commands
    INSERT  #tOutput(OutputText)
    SELECT  'UPDATE ' + @TableName + ' SET ' 

    INSERT  #tOutput(OutputText)
    SELECT  '[' + Column_Name + '] = ' + 'LTRIM(RTRIM([' + Column_Name + '])), ' 
    FROM    INFORMATION_SCHEMA.Columns 
    WHERE   Table_Name = @TableName 
       AND Data_Type LIKE '%CHAR%' 

    --      Trim last comma
    UPDATE  #tOutput
    SET             OutputText = LEFT(OutputText, LEN(OutputText)-1)
    WHERE   RowID = (SELECT Max(RowID) FROM #tOutput)

    -- use subselect to concatenate the command string
    SELECT  @OutputString = ISNULL(@OutputString, '') + ISNULL(OutputText, '')
    FROM    
                    (SELECT OutputText
                    FROM    #tOutput
                    ) TextOutput
    -- run the command
    EXEC    sp_ExecuteSQL @OutputString