在 SQL 输出中插入双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6253814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert double quotes into SQL output
提问by Ray
After I run a query and view the output, for example
例如，在我运行查询并查看输出后
select * from People
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?
或者有没有办法在 SQL 中执行此操作来修改记录以包含引号？
Because when you export, it's going to include the commas anyway, right?
因为当您导出时，它无论如何都会包含逗号，对吗？
回答by Joe Stefanelli
If the columns you're interested in are 128 characters or less, you could use the QUOTENAMEfunction. Be careful with this as anything over 128 characters will return NULL.
如果您感兴趣的列不超过128 个字符，则可以使用QUOTENAME函数。小心这一点，因为任何超过 128 个字符的内容都会返回NULL。
SELECT QUOTENAME(First, '"'), QUOTENAME(Last, '"'), QUOTENAME(Email, '"')
FROM People
回答by Blindy
select '"'+first+'","'+last+'","'+email+'"'
from people
This is the kind of thing best done in code however, you shouldn't queryfor presentation.
这是怎样的代码做最好不过，你不应该的事情询问了演示。
回答by MikeyKennethR
select concat(“\"”,first,“\"”,“\"”,Last,“\"”,“\"”,Email,“\"”) as allInOne
选择 concat("\"",first,"\"","\"",Last,"\"","\"",Email,"\"") as allInOne
回答by Jonathan Leffler
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):
在标准 SQL 中，您将使用双引号将单引号括起来（对双引号没有特殊处理）：
'"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:
某些 DBMS 允许您在字符串周围使用双引号（在标准 SQL 中，双引号表示“分隔标识符”；SQL Server 为此使用方括号），在这种情况下，您可以将字符串写为：
"""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.
不过，通常情况下，您的导出工具提供 CSV 输出格式，您的 SQL 语句无需担心。嵌入式引号使其他任何问题都成为问题。实际上，您通常不应该让 DBMS 处理数据的格式化。
回答by Badly-Bent
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
回答by Josh McCoy
If you are using MS SQL Server, try something like:
如果您使用的是 MS SQL Server，请尝试以下操作：
SELECT '"'||Table.Column||'"'
FROM Table
-- Note that the first 3 characters between "SELECT" and "||" are: ' " '
-- 注意“SELECT”和“||”之间的前3个字符 是： ' ” '
-- The characters are the same after "||" at the end... that way you get a " on each side of your value.
--“||”后的字符相同 最后......这样你就会在你的价值的每一边得到一个“。
