TRIMing the entire table in one shot

Question

TRIMing the entire table in one shot

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I really don't recommend that method. It will run a scan update for EACH COLUMN instead of once for the table. Try that on 5 M rows and 30 columns and see how long the system goes down . Seriously you could have written that damn code 5000 times in the time you spent trying to find a better way. A programmer should be lazy as to find the easiest way for the server/application to do the job... not to save from typing a few more key strokes.

Curly SSC Rookie Points: 41 More actions · Answer 2

You are correct... Didn't notice mention of how large the tables are?

You can rewrite the inner loop to make a single update by continuing to append the set values for each column into a single sql statement.

The id # are in the syscolumns table, but you don't want to trim a numeric field.

Kushal Koolwal Ten Centuries Points: 1080 More actions · Answer 3

Thanks guys. So waht should the final code look like after your last suggestion about the inner loop.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Set @sql = 'Update ' + @TableName + ' SET '

while Fetch...

@sql = @sql + @Colname + ' = LTRIM(RTRIM(' + @ColName + ')), '

end while

set @sql = LEFT(@Sql, DATALENGTH(@Sql) - 2)

exec (@Sql)

David Burrows SSC Guru Points: 65143 More actions · Answer 5

or, without a cursor

SELECT @sql = COALESCE(@sql+',','UPDATE '+@tablename+' SET ')+

COLUMN_NAME+'=LTRIM(RTRIM('+COLUMN_NAME+'))'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tablename

AND DATA_TYPE LIKE '%char'

edited

p.s. maybe some performance gain by only LTRIM char columns, not sure how much you'd gain though

Far away is close at hand in the images of elsewhere.
Anon.