CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Ray
Ray

Reputation: 3449

Insert double quotes into SQL output

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

select * from People

My output is as follows

First   Last      Email
Ray     Smith     [email protected]

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

"Ray","Smith","[email protected]"

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?

Upvotes: 22

Views: 117100

Answers (6)

Josh McCoy
Josh McCoy

Reputation: 111

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.

Upvotes: 1

Badly-Bent
Badly-Bent

Reputation: 1

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

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753990

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.

Upvotes: 2

MikeyKennethR
MikeyKennethR

Reputation: 608

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

Upvotes: 2

Blindy
Blindy

Reputation: 67380

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

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

Upvotes: 7

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

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

Upvotes: 25

Related Questions