Is there a way to remove all the spaces at the end of a SQL Server field?

Question

I imported some data into SQL server and at the end of the data in a column it added extra spaces. (Each row has a different amount of spaces). Is there a query to delete all those spaces at the end of the field?

Answer 1

You can use the rtrim command to remove trailing spaces from a varchar field.



rtrim returns a character string after truncating all trailing blanks





update Table

set field = rtrim(field)

Answer 2

I haven't played with SQL Server DTS for about a year but ...



What datatype are you importing it into? Varchar or char?



Varchar will trim, char will pad.



We always import into varchar and then do a non-destructive conversion later.



************



Just read previous answer.



Ltrim is not a T-SQL function. You can use a transformation task in a DTS task, but these use VB transformations, and can be very slow. I would still recommend landing your data using varchar into a simple table. This shouldn't pad your field unless you have used char. You can then use a proper T-SQL execute SQL task to carryout any transformations you need





Search microsoft books online for all the key words and function for the version of SQL server you are using.



at --- http://msdn.microsoft.com/en-gb/library/bb431895.aspx



The above info refers to SQL Server 2000, and the best practice we use when we load large daily files into our databases.



You may be able to get away with a simple transformation for small files.



Hope this helps.

Answer 3

If the column is defined as fixed length, then no (but for display purposes, you can retrieve the data as RTRIM(fieldName) to strip off the excess before using it.) If the field was defined VARCHAR, you could update the row with the trimmed value. You could either do this via an updatable cursor or, if you have a unique key for each row, you could do it with a correlated subquery (which may be a CPU hog, so be warned):



UPDATE someTable a

SET a.someField =

(SELECT RTRIM(b.someField) FROM someTable b

WHERE a.someKey = b.someKey)

Answer 4

there is quite of utility call chili comfortable which will execute asp on a linux server. i'm now no longer constructive how ideal it runs yet you may learn the item below for further suggestions. With Chili comfortable, you will possibly desire to be waiting to get get entry to to information from sq. Server 2000, yet you're transforming into a complicated time working sq. Server indoors Linux seeing it extremely is compiled for domicile domicile windows. there's a domicile domicile windows emulator equipment talked approximately as Wine which will run many domicile domicile windows purposes yet i think of sq. Server would be quite too complicated for it.