Deleting extra blanks from MSSQL table cells

Can it be done?

[webboy1]

I have a text file which i have converted over to a MSSQL Database table. Once converted to a SQL table there is a lot of white space after the actual data. For example, the data is entered into the cell as:

'celldata ' rather than
'celldata'

Unfortunately there are over 3000 rows, so manually getting rid of these extra spaces would be a pain.

I have also tried copying the data from one table to another, but using the rtrim() function in the script ..... but it doesn't seem to get rid of the white space. I guess this is because it thinks the spaces are characters.

Does anyone know of a more efficient way to get rid of these extra spaces without manually going through 3K + rows?

Cheers,
Webboy

[mattglet]

Rather than RTRIM(), have you tried TRIM()?

[Easy_Coder]

what's that underlying datatype on the table for this data? It sounds like it is defined as char(n).

So if you've have char(20) defined as an example and TEST is the value you should expect TEST to look like this:

'TEST    ' 

try doing this in query analyzer and see if it's still padded.

select cast(ColName as varchar(20)) as [sample] from [yourTableName]