How can I remove trailing spaces from a SQL Server

Question

Here's the problem. I run a query in SQL Server Management Studio. I click "Save results As..." and save the file as CSV. When I open the CSV file (with Notepad or any text editor), trailing spaces have been added on every column to make them a uniform width. This is extremely frustrating, because when I open the file with Excel, it auto-converts the fields into columns, changing account numbers and such to scientific notation.

With SQL Server 2005, there are no trailing spaces added, so Excel just puts all the data in a single column. Then I can convert text to columns and specify every column to be Text. But my company has switched me to SQL Server 2008 and now the only way to get the correct formatting is to import the CSV into Access, run Trim functions in Access (and thus change the field names) to get rid of the spaces, then export from Access to Excel. PLEASE HELP!!! Why the heck is SQL Server 2008 adding trailing spaces and where is the option to make it stop???

SOLVED: Please see my answer below.

Answer 1

Although I haven't figured out why a CSV created by SQL Server 2008 behaves differently in Excel than one created in SQL Server 2005, I have found a solution to my immediate problem.

First of all, the trailing spaces don't seem to be the problem. However, SS2008 is adding them where SS2005 does not and it is annoying. But this is not the main cause of my problem.

When right clicking the resulting CSV file then clicking Open With -> Excel, it was autoformatting into badly formatted columns (where SS2005's CSV file opens with all the data in a single column). To solve this, I just open Excel first, then open the CSV file from within Excel. This gives me the column formatting dialogue I was missing.

Answer 2

Maybe the problem is that you are using char and nchar in tables schema instead of varchar and nvarchar. Changing the column types might have as consequence that the generated CSV's have no trailing spaces anymore.

Here is a link to the types difference: What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

Answer 3

You can just use ltrim and rtrim in sql to trim the results, so your resultset doesn't include the trailing spaces...

select ltrim(rtrim(field) as trimmedField from table