December 5, 2005 at 12:06 pm
Dear all,
I'm inheriting a database where there are many occurances of unwanted trailing blank spaces in data in varchar columns in a lot of the tables. Does any one have a script that will loop through all the varchar columns in the database to trim the blanks?
Thanks,
Larry
December 5, 2005 at 12:41 pm
You could do a replace (below..) but why bother when you can just trim during display ?!?!
declare @trimString as varchar(10) set @trimString = 'boo ' select @trimString, datalength(@trimString) as length select replace(@trimString, ' ', ''), datalength(replace(@trimString, ' ', '')) as length
**ASCII stupid question, get a stupid ANSI !!!**
December 5, 2005 at 1:23 pm
Why not just rtrim()???
Also I do not have that script anymore (was done in a dts package).
What it did was basically this >>
For each tables in db loop
fetch the columns name that are of varchar or nvarchar from that table
create a statement like this >>
Update tablename set col1 = rtrim(col1), col2 = rtrim(col2)... where (Right(Col1, 1) = ' ' or Right(Col2, 1) = ' '...)
run statement
loop to next table
December 5, 2005 at 1:33 pm
yup - rtrim is much better - for some reason I was confusing it with right() which requires 2 arguments...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply