x

Double quotes around query resultset

I am sorry if this has been already answered somewhere but I have been searching for 1 1/2 days and I am on a deadline.

I have a query that I want to output like the following...

"123456","4444","This is the issue, sorry.","This is it---once again."

I can get the comma but I cannot get the double quotes around every field.

Please help...thanks Rob.

more ▼

asked Oct 30, 2009 at 01:02 PM in Default

Rob gravatar image

Rob
33 2 2 2

Do you need to do any escaping? Looks like you're trying to create CSV. What happens if there is a double quote in the data?
Oct 30, 2009 at 01:20 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

You can add the double quotes to each column, after converting the colunm to a character type where necessary

select  '"' + cast(col1 as varchar) + '"',  '"' + cast(col2 as varchar) + '"',  '"' + cast(col3 as varchar) + '"' from  Mytable 
more ▼

answered Oct 30, 2009 at 01:16 PM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left

I've always found the QUOTENAME() function to be easy to read and use. It doesn't get as confusing when concatenating long strings together.

 select quotename('quotename','''') -- using two single quotes select quotename('quotename','"') -- using a double quote select quotename('quotename','[]') -- using brackets 
more ▼

answered Oct 30, 2009 at 02:14 PM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

Not sure exactly what trouble you're having - it would probably help if you posted your query. Are you looking for something like this?

SELECT  '"' + name + '", "' + CAST(database_id AS varchar(10)) + '"'
FROM    sys.databases

You can also use SSIS to output into almost any format that you can imagine.

more ▼

answered Oct 30, 2009 at 01:19 PM

Aaron Alton gravatar image

Aaron Alton
575 2

(comments are locked)
10|1200 characters needed characters left

You an also change the behavior of double quotes with set quoted_identifier http://msdn.microsoft.com/pt-br/library/ms174393.aspx

Some examples that could be usefull:

set quoted_identifier off select "123456","4444","This is the issue, sorry.","This is it---once again." select """123456""","""4444""","""This is the issue, sorry.""","""This is it---once again."""set quoted_identifier on select '123456','4444','This is the issue, sorry.','This is it---once again.' select '"123456"','"4444"','"This is the issue, sorry."','"This is it---once again."' 
more ▼

answered Oct 30, 2009 at 01:54 PM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

We just found that in SQL Developer, if you use the following it incoorporates the double quotes on each field. We are using this method in a unix script.

select '"' || field1 || '"' from tablename; returns "field1"

select '"' || field1 || '"' , '"' || field2 || '"' from tablename returns

         "field1" "field2" (in two columns of course.)
more ▼

answered Feb 21, 2013 at 08:08 PM

RogerLiedeke gravatar image

RogerLiedeke
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x371

asked: Oct 30, 2009 at 01:02 PM

Seen: 9329 times

Last Updated: Feb 21, 2013 at 08:08 PM