Menu
I get a lot of database information from clients in excel spreadsheets. I frequently need to insert/update this data back into the database.
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.
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?
764
Answers. An apostrophe at the beginning of a cell value is an indicator that the value should be treated as text even if it looka like a number or a date. Such an apostrophe will not be displayed. If you want to display an apostrophe at the beginning of a cell value, type two apostrophes.
Microsoft Excel uses double quotation marks to signify text within formulas. When it sees these marks, it uses the text and discards the quotes. Typing quotation marks directly into a cell is not an issue because Excel automatically recognizes that you are entering text and therefore keeps the quotation marks.
To insert single quotes around the cell values, please apply this formula: ="'" & A1 & "'".
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.
190
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With