开发者

Insert double quotes into SQL output

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

select * from People

My o开发者_如何学运维utput 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?


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


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

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


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


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.


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜