Adding double quotes in SSMS 2014 Results (Response Needed from Original Post)

Question

I have been reading over the answers to the question posed here:

How to get export output in "real" CSV format in SQL Server Managment Studio?

I find the post by Joel Coohoern most useful, but have a follow-up question. My rep is not sufficient to post a comment, so I pose a question (hopefully that does not annoy too much).

But why would one want to use the replace function to add in double quotes to already in place single quote? The suggested formula below:

SELECT '"' + REPLACE(CAST(column AS NVARCHAR(4000)), '"', '""') + '"' AS Header, ... FROM ...

For instance if there was an entry in a field that was John "J, then the formula above would make it "John ""J". Why would one want it that way as opposed to "John "J". I am just trying to grasp the utilization of the replace function in the previous post.

Thanks.

Answer 1

A double quote is the escape character. So if they want to literally have a double quote then they have to escape.

It's easier to understand of you create a CSV with 2 lines and then open it in something like excel or some csv viewer.

"John "J", "test1"
"John ""J", "test2"

Here's a screenshot of what it looks like in excel and a csv viewer