SET QUOTED_IDENTIFIER

最新推荐文章于 2020-11-17 18:36:08 发布

85后的狮子

最新推荐文章于 2020-11-17 18:36:08 发布

阅读量552

点赞数

文章标签： table insert object string

本文链接：https://blog.youkuaiyun.com/dragon611/article/details/3723047

版权

使 Microsoft® SQL Server™ 遵从关于引号分隔标识符和文字字符串的 SQL-92 规则。由双引号分隔的标识符可以是 Transact-SQL 保留关键字，或者可以包含 Transact-SQL 标识符语法规则通常不允许的字符。

语法

SET QUOTED_IDENTIFIER { ON | OFF }

示例

A. 使用被引用的标识符设置和保留字对象名

下例显示 SET QUOTED_IDENTIFIER 设置必须为 ON，而且表名内的关键字必须在双引号内，才能创建和使用带保留关键字的对象名。

SET QUOTED_IDENTIFIER OFF
GO
-- Attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" int IDENTITY, "order" int)
GO

SET QUOTED_IDENTIFIER ON
GO

-- Will succeed.
CREATE TABLE "select" ("identity" int IDENTITY, "order" int)
GO

SELECT "identity","order" 
FROM "select"
ORDER BY "order"
GO

DROP TABLE "SELECT"
GO

SET QUOTED_IDENTIFIER OFF
GO

B. 在被引用的标识符设置中使用单引号和双引号

下例显示将 SET QUOTED_IDENTIFIER 设置为 ON 和 OFF 时，在字符串表达式中使用单引号和双引号的方式。

SET QUOTED_IDENTIFIER OFF
GO
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'Test')
   DROP TABLE Test
GO
USE pubs
CREATE TABLE Test ( Id int, String varchar (30) ) 
GO

-- Literal strings can be in single or double quotation marks.
INSERT INTO Test VALUES (1,"'Text in single quotes'")
INSERT INTO Test VALUES (2,'''Text in single quotes''')
INSERT INTO Test VALUES (3,'Text with 2 '''' single quotes')
INSERT INTO Test VALUES (4,'"Text in double quotes"')
INSERT INTO Test VALUES (5,"""Text in double quotes""")
INSERT INTO Test VALUES (6,"Text with 2 """" double quotes")
GO

SET QUOTED_IDENTIFIER ON
GO

-- Strings inside double quotation marks are now treated 
-- as object names, so they cannot be used for literals.
INSERT INTO "Test" VALUES (7,'Text with a single '' quote')
GO

-- Object identifiers do not have to be in double quotation marks
-- if they are not reserved keywords.
SELECT * 
FROM Test
GO

DROP TABLE Test
GO

SET QUOTED_IDENTIFIER OFF
GO

下面是结果集：

Id          String                         
----------- ------------------------------ 
1           'Text in single quotes'        
2           'Text in single quotes'        
3           Text with 2 '' single quotes   
4           "Text in double quotes"        
5           "Text in double quotes"        
6           Text with 2 "" double quotes   
7           Text with a single ' quote