如何在 sql 语句 where 子句中使用 Char(39)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14657056/
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
How do I use Char(39) in a sql statement where clause?
提问by Jamie Colville
I am using SQL Server 2008 R2. I am trying to create a recordset based off a sql statement. In the WHEREclause is 'INTERNATIONAL PEOPLE' + Char(39) + 'S'. So the single quote makes it PEOPLE'S. This is failing. From research it seems this should not fail. Any help would be great. Thanks
我正在使用 SQL Server 2008 R2。我正在尝试基于 sql 语句创建记录集。在WHERE子句'INTERNATIONAL PEOPLE' + Char(39) + 'S'。所以单引号使它成为人们的。这是失败的。从研究看来，这不应该失败。任何帮助都会很棒。谢谢
回答by Hamlet Hakobyan
You can escape single quote by single quote :)
您可以通过单引号转义单引号:)
WHERE field = 'INTERNATIONAL PEOPLE''S'
回答by ThinkingStiff
From your sample in a comment, I'm guessing it's because your table is named 1099and the error is not really from your WHEREclause. T-SQL expects table names to start with an alpha character.
从您在评论中的示例中，我猜这是因为您的表已命名1099并且错误并非真正来自您的WHERE子句。T-SQL 要求表名以字母字符开头。
Try putting brackets around that:
尝试将括号括起来：
SELECT * FROM [1099] WHERE Name ='INTERNATIONAL PEOPLE' + CHAR(39) + 'S'
回答by deeg
Can you post your query? A escaping a single quote -''- should work. Test with the code below.
你能发表你的查询吗？转义单引号 -''- 应该有效。用下面的代码测试。
IF OBJECT_ID('TempDB..#SingleQuoteTest') IS NOT NULL
BEGIN
DROP TABLE #SingleQuoteTest
END
CREATE TABLE #SingleQuoteTest
(
Value VARCHAR(MAX)
)
INSERT INTO #SingleQuoteTest
(
Value
)
SELECT 'International People'
UNION SELECT 'International People''s'
UNION SELECT 'John O''connor'
SELECT Value
FROM #SingleQuoteTest
WHERE Value = 'International People''s'
--WHERE Value = 'John O''connor'
回答by TLEMMA
The apostrophe s is essential element in writing reports. Here is an example. It gives the possessive property of a person in this case.
撇号 s 是撰写报告的基本要素。这是一个例子。在这种情况下，它给出了一个人的所有物。
SELECT TitleAndLastName = (Title + ' ' + LastName +CHAR(39) +'s' + ' ' + 'address is:'), FullAddress = (AddressLine1 + City + cast(StateProvinceID as nvarchar) + PostalCode)
FROM [Person].[Person] as p
Inner Join [Person].[Address] as a
on a.AddressID = p.BusinessEntityID
Where AddressLine1 is not null
and Title is not null;
The result of the above query will have the ('s) of each person's last name.
上述查询的结果将包含每个人的姓氏 ('s)。
TitleAndLastName FullAddress
标题和姓氏完整地址
Ms. Erickson's address is: 1226 Shoe St.Bothell 79 98011
埃里克森女士的地址是：1226 Shoe St.Botell 79 98011
Mr. Goldberg's address is: 1399 Firestone DriveBothell 79 98011
Goldberg 先生的地址是：1399 Firestone DriveBothell 79 98011
Good luck!
祝你好运！
