在SQL输出中插入双引号。

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

例如，在运行查询并查看输出之后

select * from People

select *的人

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?

或者，在SQL中是否有一种方法可以将记录修改为包含引号?

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

因为当你导出时，它会包含逗号，对吧?

6 个解决方案

#1

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.

如果您感兴趣的列是128个字符或更少，您可以使用QUOTENAME函数。要小心，因为超过128个字符将返回NULL。

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

#2

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

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

这是最好在代码中完成的事情，您不应该查询表示。

#3

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

选择concat(“\”,首先,“\”,\“”,最后,“\”,\“”,电子邮件,“\”)作为allInOne

#4

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处理数据的格式。

#5

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

#6

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”和“||”之间的前三个字符是:“”

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

——结尾“||”后的字符相同……这样，你的价值每一面都有一个“a”。

#1