0

I have a SQLCMD below (it works fine) that I execute from command line to write SQL data to a .csv file. I was having an issue with commas that are inside database values (which ruins the csv separations) by casting the string field to an nvarchar(100) surrounded by escaped-double quotes, like this:

SQLCMD -s"," -S servername -U username -d databasename -W -o 
"mydatafile.csv” -Q "SET NOCOUNT ON 
SELECT Account,'""' + cast ([ColDescription] as nvarchar(100)) + '""' as ColumnDescription" FROM myTable

...the column with the escaped double-double quotes is ColDescription, and the original string field actually has 100 characters. So, this fixed my issue but now I am facing a new issue with my database values that start with quotes. For those records where the data value actually starts with quotes, I assume I would have wanted to escape with 3 instances of double-quotes (so that the result string will have all double-quotes escaped).

But, how can I tell SQL to escape any number of instances of double-quotes? I don't know which records will have double-quotes, or 2 double-quotes, or what.

Better yet, I would ideally like to display the double-quotes in my .csv file if they existed in the data. So maybe escaping them altogether isn't what I need. I'm not sure that I'm researching the right words for this problem either. I think I want to escape a pattern of characters...does this sound like a REGEX thing? I haven't used REGEX in sql before and my instinct tells me that's too complicated for an issue that seems common and must have been solved before.

EDIT: I also tried a new approach as I made a comment about below. I changed my SQLCMD to the following (although this is a stripped-down version of my "real" query). But notice I used the Replace function to replace all quotes with 2 quotes. I thought that by making an even number of quotes, all of them will be escaped in the .csv export.

SQLCMD -s"," -S servername -U username -d databasename -W -o 
"mydatafile.csv” -Q "SET NOCOUNT ON 
SELECT Account,'""' + Replace(cast ([ColDescription] as 
nvarchar(100)),'"','""') + '""' as ColumnDescription" FROM myTable

...But now my error in the command prompt is "Unexpected Argument." I think "Replace" is a keyword that maybe I would also need to escape in windows...? The new Select statement works fine directly in SQL, though. I just can't execute the command anymore.

PBJ
  • 354
  • 2
  • 15
  • I also tried replacing all double-quotes with 2 double-quotes (using the replace function), so that there are always an even number of double-quotes. But then my command doesn't run from what appears to be a syntax issue (although the SQL query itself works fine). I was hoping that this would escape all quote-issues. – PBJ Mar 20 '19 at 17:51
  • I am not sure about SQLCMD, but I think double quotes need not be escaped in tsql. Can you provide an example of a case that doesn't work and what it returns? – George Menoutis Mar 20 '19 at 18:01
  • @GeorgeMenoutis thank you for responding. I updated my OP with new detail. Also, the quote character may not need escaped in SQL, but it needs escaped in CMD because I can't run the command in CMD without the doubled-up double quotes. (I tried it an I received 'unexpected error'), which is the same error I received when I used a Replace function like I described above. It's an endless road of new problems! – PBJ Mar 20 '19 at 18:18
  • How about this: in order to avoid both the sql single quotes and the cmd double quote, instead of a double quote `'""'`, use char(34) (or nchar(34) for unicode version) – George Menoutis Mar 20 '19 at 22:06
  • That doesn't seem to fix the issue, and I would need the whole 100 characters worth of the field anyway. Thanks for the suggestion, though. – PBJ Mar 21 '19 at 12:56

1 Answers1

0

I finally found a way around my issues. If I use the QUOTENAME function around my SQL description columns that may contain special characters like commas and double-quotes, then I can export to a CSV file and the comma-separations will work properly, and quotes within the data fields are also preserved. My new SQL statement/SQLCMD statement is:

SQLCMD -s"," -S servername -U username -d databasename -W -o 
"mydatafile.csv” -Q "SET NOCOUNT ON 
SELECT Account, QUOTENAME(cast ([ColDescription] as nvarchar(100)),'""') as ColumnDescription" FROM myTable

I read about this QUOTENAME function before but couldn't quite get it to work, and I found an example of using QUOTENAME here:

Exporting CSV data using SQLCMD.EXE

But I had to change that solution slightly (it gave me CMD error again like 'Unexpected Argument Press ? for help'). As you can see above, I am now using 2 double-quotes instead of one in the QUOTENAME function. That fixed everything.

PBJ
  • 354
  • 2
  • 15