在 varchar 变量 SQL Server 2008 中存储单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7745645/
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
Storing single quotes in varchar variable SQL Server 2008
提问by Eric R.
I was wondering if there is a way to store single quote marks in SQL Server 2008. I am building several reports up and all these reports are the exact same, except they only differ in the codes I am selecting upon. For example, one report uses codes 'abc', 'def', 'ghi' and another report uses codes 'jkl', 'mno', 'pqr'. I was thinking to reduce the number of Stored Procedures I will have to make, I could make a parameter on the report to choose which type of report to run. Based off of that, I would use the correct codes. So I was going to store these codes in a varchar variable. Below is the functionality I was hoping for:
我想知道是否有一种方法可以在 SQL Server 2008 中存储单引号。我正在构建多个报告，所有这些报告都完全相同，只是它们仅在我选择的代码上有所不同。例如，一份报告使用代码“abc”、“def”、“ghi”，而另一份报告使用代码“jkl”、“mno”、“pqr”。我想减少我必须制作的存储过程的数量，我可以在报告上设置一个参数来选择要运行的报告类型。基于此，我会使用正确的代码。所以我打算将这些代码存储在一个 varchar 变量中。以下是我希望的功能：
DECLARE @codes1 varchar, @codes2 varchar
SET @codes1 = ''abc', 'def', 'ghi''
SET @codes2 = ''jkl', 'mno', 'pqr''
Then, I was going to use the proper varchar variable based on the parameter the user chooses. The only problem is setting the variables since the string will have single quotes in it (the string will be used in an SQL 'IN' statement, thats the reason for the single quotes being present).
然后，我将根据用户选择的参数使用正确的 varchar 变量。唯一的问题是设置变量，因为字符串中将包含单引号（该字符串将用于 SQL 'IN' 语句中，这就是存在单引号的原因）。
回答by John Hartsock
Like this. Yes Oded is correct. The proper terminology for this is 'escaping'. You can escape a single quote 'by doubling it up ''
像这样。是的，奥德是正确的。对此的正确术语是“转义”。您可以'通过加倍来转义单引号''
DECLARE @codes1 varchar(50), @codes2 varchar(50)
SET @codes1 = '''abc'', ''def'', ''ghi'''
SET @codes2 = '''jkl'', ''mno'', ''pqr'''
回答by JeffO
Try to avoid hard-coding values. Create a table to hold these values along with a way to group them.
尽量避免硬编码值。创建一个表来保存这些值以及对它们进行分组的方法。
Table CodeGroups
GroupNumber | Codes
1 | abc
1 | def
1 | ghi
2 | kkl
2 | mno
2 | pqr
This way the user only has to select GroupNumber = 1
这样用户只需选择 GroupNumber = 1
You link the CodeGroups table to the table with the code strings. All you have to do to add another code is make an entry in this table and give it a group number.
您将 CodeGroups 表链接到带有代码字符串的表。添加另一个代码所需要做的就是在此表中输入一个条目并为其指定一个组号。
回答by Ahishesh
Avoid single qoute problem just by doubling it.
只需将其加倍即可避免单个 qoute 问题。
Value Varchar2(10):= ''abc''; ---You will get error.
值 Varchar2(10):= ''abc''; ---你会得到错误。
Value Varchare(10):= '''abc'''; ---Will Solve your problem.
值 Varchare(10):= '''abc'''; ---将解决您的问题。
回答by law-developer
int varId = "1";
String varItem = "Google's root";
String strSQL = "INSERT INTO table("
+ "id,"
+ "item"
+")"
+ "VALUES('" + varId
+ "', '" + varItem
+ "')";
