Trim all data in a table

[scabral79]

Hi,

i have a table with 68 columns. I want to do a ltrim(rtrim(column)) for all the columns in the table.

Is there a way to automate this somehow? perhaps create some sort of loop to go through each column and to do the ltrim and rtrim?

this will be run on a monthly basis through dts package.

thanks
Scott

 

[bborissov]

Why not make these columns varchar?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[jdaman]

There is no need to loop anything.

update <your table>
set <column1> = ltrim(rtrim(<column1>)),
<column2> = ltrim(rtrim(<column2>)),
...
<column68> = ltrim(rtrim(<column68>))

However, I would think you would better off validating the data when created rather than scrubbing it after the fact.

 

[bborissov]

[COLOR=blue]declare[/color] @sql [COLOR=blue]varchar[/color](8000)
[COLOR=blue]declare[/color] @column [COLOR=blue]varchar[/color](8000)
[COLOR=blue]SELECT[/color] @column = [COLOR=#FF00FF]MIN[/color](column_name)
       [COLOR=blue]from[/color] INFORMATION_SCHEMA.Columns
[COLOR=blue]WHERE[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'char'[/color], Data_Type) > 0 AND
      Table_Name = [COLOR=red]'YourTable'[/color]

[COLOR=blue]SET[/color] @sql = [COLOR=red]'UPDATE YourTable SET '[/color]
[COLOR=blue]WHILE[/color] @column [COLOR=blue]IS[/color] NOT NULL
      [COLOR=blue]BEGIN[/color] 
         [COLOR=blue]SET[/color] @sql = @sql + @column+[COLOR=red]' = LTRIM(RTRIM('[/color]+@column+[COLOR=red]')),'[/color]
         [COLOR=blue]SELECT[/color] @column = [COLOR=#FF00FF]MIN[/color](column_name)
                [COLOR=blue]from[/color] INFORMATION_SCHEMA.Columns
         [COLOR=blue]WHERE[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'char'[/color], Data_Type) > 0 AND
               Table_Name = [COLOR=red]'YourTable'[/color]         AND
               Column_Name > @column
     [COLOR=blue]END[/color]
[COLOR=blue]SET[/color] @sql = [COLOR=#FF00FF]LEFT[/color](@sql, LEN(@sql)-1)
[COLOR=blue]EXEC[/color] (@sql)

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP