Commas in the result set

I am using SQL 2005 and have a query that returns seven columns of data. In one the columns, the results contains commas. For example, one of the results is 'brooke, nick, dustin, and me.jpg'. The results are saved into a .csv file so it can be imported into Excel. The user named the file with the commas and I need to preserve the original file name.

My question:

Is there some way to format the data inside of SQL so that when it gets saved as a .csv file, the extra commas don't throw off the column layout?

I don't want to have to manually clean up possibly several hundred rows of data everytime I run the query.

Thanks in advance.

As I recall, you need to wrap the string in double quotes, and then double up any quotes that appear inside the string. Something like this:

SELECT '"' + REPLACE(col, '"', '""') + '"' AS col FROM ...

Do all of your varchar columns like that and you should be in the clear as far as Excel is concerned.
|||

I am not sure how you are importing the data into CSV. The CSV columns can have Commas, yes you can survie with comma, enclose the values with Double Quotes.

sample,

"1", "brooke, nick, dustin and me.jpg", "col3 value", "col4 value"

"2", "dustin and me.jpg", "col3 value", "col4 value"

here the sample C# code,

String.Format("\"{0}\", \"{1}\", \"{2}\"\n", col1.Replace("\"", "\"\""), col2.Replace("\"", "\"\""), col3.Replace("\"", "\"\""));

|||

I did use the Replace function to replace the commas with an extra space in the result set instead of trying to preserve the comma. I tried several different ways to keep the comma but none of them worked. The data is manually imported into Excel. The solution will work for now.

Thanks so much for your help!