双引号可以用来分隔字符串吗? [英] Can double quotes be used to delimit a string?

问题描述

我惊讶地发现 SQL 2008 T-SQL proc 使用双引号作为字符串分隔符.例如:

I was surprised to encounter a SQL 2008 T-SQL proc that used a double quote as a string delimiter. For example:

DECLARE @SqlStmt AS VarChar(5000)
SET @SqlStmt = "ok"
PRINT @SqlStmt

我不认为这是允许的.我以为只能使用撇号.

I didn't think it was allowed. I thought only apostrophes could be used.

认为 T-SQL 像 Javascript 一样灵活，允许作为分隔符，以防您想在单个语句中混合两者，我尝试在我的一个存储过程中做同样的事情，因为我想构建动态 SQL包括蜱.我很惊讶脚本无法编译，我收到了无效列 [X]"错误，其中 X 是我引用的字符串的内容.

Thinking that T-SQL is flexible like Javascript to allow either as delimiters in case you wanted to mix the two in a single statement, I tried to do the same in one of my stored procs because I wanted to construct dynamic SQL that included ticks. I was surprised that the script wouldn't compile, I was getting an "INVALID COLUMN [X]" error where X was the contents of my quoted string.

因此，我将其分解为最简单的组件，直到得到您在上面看到的确切 SQL，它可以编译、运行并打印OK".

So, I stripped it down to it simplest components until I got the exact SQL you see above, which compiles, runs, and prints "OK".

现在是真正令人惊讶的部分:

Now the real surprising part:

我将上面的 T-SQL 脚本放在我的剪贴板上，执行 Control + N 以使用相同的连接打开一个新的查询窗口，并将上面的脚本粘贴到新的查询窗口中，并惊讶地看到我得到了以下内容新窗口中的错误:

I placed the T-SQL script above on my clipboard, executed Control + N to open a new query window using the same connection and pasted the above script into the new query window and was shocked to see that I was getting the following error in the new window:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'ok'.

说明更新:

显然我已经从原始脚本中删除了 SET QUOTED_IDENTIFIER，但是我已经在原始查询窗口中执行了它，但是该选项仍然有效.当我创建一个新的查询窗口时，该选项默认重置为关闭.

Apparently I had removed the SET QUOTED_IDENTIFIER from the original script, but I had already executed it in the original query window, but the option was still in effect. When I created a new query Window, the option was reset to off by default.

感谢您的回答.

推荐答案

If QUOTED_IDENTIFIER 是 OFF 然后它们可用于分隔字符串.否则双引号中的项目将被解释为对象名称.

If QUOTED_IDENTIFIER is OFF then they can be used to delimit a string. Otherwise items in double quotes will be interpreted as object names.

对于需要分隔的对象名称，始终使用单引号分隔字符串和方括号，这样您的代码在错误"设置下运行时不会中断.

Always use single quotes to delimit strings and square brackets for those object names that require delimiting so your code doesn't break when run under the "wrong" setting.

这篇关于双引号可以用来分隔字符串吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！