Insert double quotes into SQL output

Question

After I run a query and view the output, for example

select * from People

My output is as follows

First   Last      Email
Ray     Smith     raysmith@whatever.itis

How would I export this data so that it looks as follows?

"Ray","Smith","raysmith@whatever.itis"

Or is there a way to do this within SQL to modify records to contain quotes?

Because when you export, it's going to include the commas anyway, right?

I wonder who upvoted a question about modifying the *records* to contain quotes so it's "easier" to export to csv... — Blindy, Jun 06 '11 at 14:51
export to csv - http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd — jason saldo, Jun 06 '11 at 15:00
I agree with the implication @Blindy made in his comment: don't change the records to make export to CSV easier. You're going to end up with more than you bargained for with statements like `select * from people where Last like '"Ander%"'` Does your flavor of SQL do a CSV export? Many of them do, and you will find it much easier to use a built-in export function instead of rolling your own. If you're using Microsoft T-SQL, take a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87448 for an export-to-CSV script. — rajah9, Jun 06 '11 at 15:08
I know this is pretty old, but just an update for anyone who is viewing this topic. I needed the data in this format for an ETL, but in no way am I modifying the records in the original table (if I had to modify them). I always modify records in backup tables, as the original ones are constantly being referred to by internal apps. — Ray, Sep 28 '11 at 17:50

score 24 · Accepted Answer · answered Jun 06 '11 at 14:52

24

If the columns you're interested in are 128 characters or less, you could use the QUOTENAME function. Be careful with this as anything over 128 characters will return NULL.

SELECT QUOTENAME(First, '"'), QUOTENAME(Last, '"'), QUOTENAME(Email, '"')
    FROM People

answered Jun 06 '11 at 14:52

Joe Stefanelli

132,803
19
237
235

I think he wants the output in a single column, separated by commas. – Blindy Jun 06 '11 at 14:55
@Blindy: It's not clear. In the question, the OP states "Because when you export, it's going to include the commas anyway". – Joe Stefanelli Jun 06 '11 at 14:57
@Blindy - I think he wants them in separate columns. – Ash Burlaczenko Jun 06 '11 at 14:57
Heh sounds like a bad assumption on his part, depending on his choice of "exporting". – Blindy Jun 06 '11 at 14:58
Sorry for the last response, Joe. That function worked perfectly! Thank you! – Ray Jun 30 '11 at 10:32
@Joe, Bindy's answer below is correct. Your answer is dangerous. Tellng people to use QUOTENAME is going to bite someone who does not read your disclaimer or the QUOTENAME docs, and only tests on short strings. QUOTENAME is there to make column names, not to concatenate quotes onto strings. – mike Aug 17 '16 at 20:26

score 7 · Answer 2 · answered Jun 06 '11 at 14:54

7

select '"'+first+'","'+last+'","'+email+'"'
from people

This is the kind of thing best done in code however, you shouldn't query for presentation.

answered Jun 06 '11 at 14:54

Blindy

65,249
10
91
131

pretty strong statement. There are times when formatting in SQL is extremely useful, allowing a direct transfer from recordset to bound control. Another example would be the GUI tool in SQL server for dumping files, where exactly what is queried ends up in the file, so all formatting must be done in the SQL. – mike Aug 17 '16 at 20:23
3

The first example is exactly what I'm talking about, it's not the database layer's job to make sure your view displays the correct data, it's the view's job to present the structured data it receives. For example, in WPF, this would be done by binding to the model and using a converter. The 2nd example is an exception, I agree, in that you're using the SQL statement as the application itself, rather than simply a layer of it. – Blindy Aug 18 '16 at 14:11

score 2 · Answer 3 · answered Jun 06 '11 at 14:58

2

select concat(“\"”,first,“\"”,“\"”,Last,“\"”,“\"”,Email,“\"”) as allInOne

answered Jun 06 '11 at 14:58

MikeyKennethR

600
4
16

sorry - but SQL should be the same - just differences in function name – MikeyKennethR Jun 06 '11 at 15:00
Only works in sql 2012, 2008 doesn't have the concat function. – Fourth Jul 23 '13 at 13:26
Being pedantic but OP wanted double quotes so in SQL Server its: Select Concat('"',[First],'","',[Last],'","',[Email],'"') – HostMyBus Jun 05 '17 at 14:04

score 2 · Answer 4 · answered Jun 06 '11 at 15:05

Modifying the records to contain quotes would be a disaster; you don't use the data only for export. Further, in theory you'd have to deal with names like:

 Thomas "The Alley Cat" O'Malley

which presents some problems.

In Standard SQL, you'd use doubled-up single quotes to enclose single quotes (with no special treatment for double quotes):

'"Thomas "The Alley Cat" O''Malley"'

Some DBMS allow you to use double quotes around strings (in Standard SQL, the double quotes indicate a 'delimited identifier'; SQL Server uses square brackets for that), in which case you might write the string as:

"""Thomas ""The Alley Cat"" O'Malley"""

Normally, though, your exporter tools provide CSV output formatting and your SQL statement does not need to worry about it. Embedded quotes make anything else problematic. Indeed, you should usually not make the DBMS deal with the formatting of the data.

score 0 · Answer 5 · answered Jun 12 '17 at 19:22

0

This worked best for me

SELECT 'UPDATE [dbo].[DirTree1] SET FLD2UPDATE=',QUOTENAME(FLD2UPDATE,'''')
+' WHERE KEYFLD='+QUOTENAME(KEYFLD,'''')
FROM [dbo].[Table1]
WHERE SUBSTRING(FLD2UPDATE,1,2) = 'MX'
order by 2

answered Jun 12 '17 at 19:22

Badly-Bent

1

score 0 · Answer 6 · edited Jul 13 '17 at 22:32

0

If you are using MS SQL Server, try something like:

SELECT '"'||Table.Column||'"'
  FROM Table

-- Note that the first 3 characters between "SELECT" and "||" are: ' " '

-- The characters are the same after "||" at the end... that way you get a " on each side of your value.

edited Jul 13 '17 at 22:32

O. Jones

103,626
17
118
172

answered Jul 13 '17 at 21:55

Josh McCoy

91
1
2

Many mistakes, you use simple quotes, then double quotes, you say there are spaces and there are none... hard to follow. – Fabien Jul 13 '17 at 22:20
Welcome to Stack Overflow. Lines indented with four spaces show as code -- as literal values. This is handy' you don't have to explain what characters go where. – O. Jones Jul 13 '17 at 22:32
The answer is now edited @Fabien if it needs more work to be correct, please point out any remaining error(s). – Josh McCoy Jul 14 '17 at 00:23
Thanks @O.Jones I appreciate the tip. – Josh McCoy Jul 14 '17 at 00:23

Insert double quotes into SQL output

6 Answers6

Linked