查询中跨越多行的 SQL 字符串值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8146368/
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
SQL string value spanning multiple lines in query
提问by Kiril
UPDATE: the bio may contain apostrophes (see updated example)
更新：生物可能包含撇号（见更新的例子）
I have an SQL query that has a value which spans multiple lines and it causes the query to fail:
我有一个 SQL 查询，它的值跨越多行并导致查询失败：
UPDATE User SET UserId=12345, Name="J Doe", Location="USA", Bio="I'm a
bio that has an apostrophe, and I'm
spanning multiple lines!"
WHERE UserId=12345
In C# you can put an @before the string Bio=@"..."in order to allow it to span multiple lines, but I'm not sure how the same thing can be achieved with SQL queries. How do you get a string to span multiple lines without having to do things like manually concatenating the strings:
在 C# 中，您可以@在字符串之前放置一个，Bio=@"..."以允许它跨越多行，但我不确定如何使用 SQL 查询实现相同的目标。如何让一个字符串跨越多行，而无需手动连接字符串之类的操作：
Bio="I'm a"
+" bio that has an apostrophe, and I'm"
+" spanning multiple lines!"
回答by Adam Wenger
SQL Server allows the following (be careful to use single quotes instead of double)
SQL Server 允许以下（注意使用单引号而不是双引号）
UPDATE User
SET UserId = 12345
, Name = 'J Doe'
, Location = 'USA'
, Bio='my bio
spans
multiple
lines!'
WHERE UserId = 12345
回答by Losbear
What's the column "BIO" datatype? What database server (sql/oracle/mysql)? You should be able to span over multiple lines all you want as long as you adhere to the character limit in the column's datatype (ie: varchar(200) ). Try using single quotes, that might make a difference. This works for me:
“BIO”列的数据类型是什么？什么数据库服务器（sql/oracle/mysql）？只要您遵守列数据类型中的字符限制（即： varchar(200) ），您就应该能够跨越多行。尝试使用单引号，这可能会有所不同。这对我有用：
update table set mycolumn = 'hello world,
my name is carlos.
goodbye.'
where id = 1;
Also, you might want to put in checks for single quotes if you are concatinating the sql string together in C#. If the variable contains single quotes that could escape the code out of the sql statement, therefore, not doing all the lines you were expecting to see.
此外，如果您在 C# 中将 sql 字符串连接在一起，您可能需要检查单引号。如果变量包含可以从 sql 语句中转义代码的单引号，因此，不要执行您期望看到的所有行。
BTW, you can delimit your SQL statements with a semi colon like you do in C#, just as FYI.
顺便说一句，您可以像在 C# 中那样用分号分隔 SQL 语句，仅供参考。
回答by Henry Aspden
with your VARCHAR, you may also need to specify the length, or its usually good to
使用您的 VARCHAR，您可能还需要指定长度，或者通常可以
What about grabbing the text, making a sting of it, then putting it into the query witrh
如何抓取文本，对其进行刺痛，然后将其放入查询中
String TableName = "ComplicatedTableNameHere";
EditText editText1 = (EditText) findViewById(R.id.EditTextIDhere);
String editTextString1 = editText1.getText().toString();
BROKEN DOWN
崩溃
String TableName = "ComplicatedTableNameHere";
//sets the table name as a string so you can refer to TableName instead of writing out your table name everytime
EditText editText1 = (EditText) findViewById(R.id.EditTextIDhere);
//gets the text from your edit text fieldfield
//editText1 = your edit text name
//EditTextIDhere = the id of your text field
String editTextString1 = editText1.getText().toString();
//sets the edit text as a string
//editText1 is the name of the Edit text from the (EditText) we defined above
//editTextString1 = the string name you will refer to in future
then use
然后使用
/* Insert data to a Table*/
myDB.execSQL("INSERT INTO "
+ TableName
+ " (Column_Name, Column_Name2, Column_Name3, Column_Name4)"
+ " VALUES ( "+EditTextString1+", 'Column_Value2','Column_Value3','Column_Value4');");
Hope this helps some what...
希望这有助于一些什么...
NOTEeach string is within
注意每个字符串都在
'"+stringname+"'
its the 'and' that enable the multi line element of the srting, without it you just get the first line, not even sure if you get the whole line, it may just be the first word
它的“和”启用了 srting 的多行元素，没有它你只会得到第一行，甚至不确定你是否得到整行，它可能只是第一个词
回答by JJ_Coder4Hire
I prefer to use the @ symbol so I see the query exactly as I can copy and paste into a query file:
我更喜欢使用 @ 符号，因此我可以像复制并粘贴到查询文件中一样查看查询：
string name = "Joe";
string gender = "M";
string query = String.Format(@"
SELECT
*
FROM
tableA
WHERE
Name = '{0}' AND
Gender = '{1}'", name, gender);
It's really great with long complex queries. Nice thing is it keeps tabs and line feeds so pasting into a query browser retains the nice formatting
对于长而复杂的查询，这真的很棒。好消息是它保留了标签和换行符，因此粘贴到查询浏览器中可以保留良好的格式
