SQL Excel 在单元格值中转义引号或撇号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26067049/
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
Excel escaping quotes or apostrophes in cell values
提问by Mir
I get a lot of database information from clients in excel spreadsheets. I frequently need to insert/update this data back into the database.
我从 excel 电子表格中的客户那里获得了大量数据库信息。我经常需要将这些数据插入/更新回数据库。
I often use excel to generate the insert and update statements via concatenating a bunch of cells together. Sometimes the data includes text cells which can have single quotes in them. If not dealt with carefully, these single quotes will make the SQL statements break.
我经常使用 excel 通过将一堆单元格连接在一起来生成插入和更新语句。有时数据包括文本单元格，其中可以有单引号。如果不小心处理，这些单引号会使 SQL 语句中断。
How can I escape single quotes in text data, via formulas, when concatenating a bunch of cell values together, so that my resulting SQL scripts are valid?
将一堆单元格值连接在一起时，如何通过公式转义文本数据中的单引号，以便生成的 SQL 脚本有效？
回答by Mir
The best solution I have found is to use the following:
我发现的最佳解决方案是使用以下方法：
=SUBSTITUTE(A1, "'", "''")
This will replace all single quotes with two single quotes which, in T-SQL statements escapes the character and treats it as string data.
这将用两个单引号替换所有单引号，这在 T-SQL 语句中会转义字符并将其视为字符串数据。
回答by user4669140
Using CONCATENATEwith double-quotes simply requires escaping the quote. That is done as follows:
使用CONCATENATE双引号只需要转义引号。这样做如下：
=CONCATENATE("""";"In quotes!";"""")
