SQL Server : escape punctuation in string

Question

I am exporting data from a SQL Server table to a .csv file, and then I use sp_send_email to email the file with data.

My problem is with this value:

Cantata Number 212 "Peasants Cantata", BWV 212

The value gets split into two columns in the .csv file that gets emailed. This value should be only in one column.

Some titles might contain a comma, which needs to be left in the string for those instances.

For example:

Cantata Number 212 Peasants Cantata"     BWV 212"

I tried this method, but is not working:

Note: This SELECT statement resides inside a view vw_WeeklyReport

SELECT TOP 100 PERCENT
    '"' + [p].[Title] + '"' [Title]
FROM
    table

The code that exports the data and emails the .csv file:

BEGIN
    SET NOCOUNT ON;

    DECLARE @qry VARCHAR(8000);

    -- Create the query, concatenating the column name as an alias
    SET @Qry = 'SET NOCOUNT ON; SELECT Title FROM [vw_WeeklyReport] SET NOCOUNT OFF';

    -- Send the e-mail with the query results in attachment.
    EXEC [msdb].[dbo].[sp_send_dbmail] 
                @profile_name = 'default',
                @recipients = '6lack@email.com',
                @subject = 'Weekly Report',
                @body = 'An attachment has been included in this email.',
                @query_attachment_filename = 'WeeklyRep.csv',
                @query = @qry,
                @attach_query_result_as_file = 1,
                @query_result_separator = ',',
                @query_result_width = 32767,
                @query_result_no_padding = 1;
END;

user_5708620 · Answer

When there are comma's (or separators) in the field, that field should be enclosed with double quotes, and any double quotes within have to be escaped with another double quote:

"Cantata Number 212 ""Peasants Cantata"", BWV 212"

Once double quotes are used around fields, all fields containing double quotes should also be quoted and inside quotes escaped as well.

Maybe you could look for an option to export to csv using quoted fields.

Removing all the comma's could also be an option, but then you lose some information.

On the other hand, if there is only one column (as in your SELECT statement) there is no need at all to use csv. A plain text file can be used instead.

user_2366842 · Answer

Change your query in the stored proc to something like this:

SET @Qry = 'SET NOCOUNT ON; SELECT replace(Title, ',', '') as Title FROM [vw_WeeklyReport] SET NOCOUNT OFF';

Note this is untested, but should give you what you're looking for. This is under the presumption that stripping out commas is acceptable, as was indicated in the initial post. If the commas need to remain intact, the answer isn't quite as simple.

SQL Server : escape punctuation in string

2 Answers