23

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?

Mir
  • 2,101
  • 1
  • 26
  • 32
  • I've thrown this up here because I keep forgetting (though I probably won't, now...) and can never find the answer quickly. – Mir Sep 26 '14 at 19:38
  • Related: If you're trying to do replacement with VBA, this question might help you. http://stackoverflow.com/questions/17430938/excel-escaping-single-and-double-quotes – Mir Sep 26 '14 at 19:38
  • 1
    Sometimes I don't want to change the actual values in the cells... but a lot of the times Find/Replace restricted to a single column or whatever is more than sufficient. – Mir Sep 26 '14 at 19:59

3 Answers3

35

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.

roland
  • 7,135
  • 6
  • 42
  • 60
Mir
  • 2,101
  • 1
  • 26
  • 32
0

Similar to Roland: 

=SUBSTITUTE(SUBSTITUTE(A1, "'", "\'"), ",", "\,")
spectre_review
  • 11
  • 3
-1

Using CONCATENATE with double-quotes simply requires escaping the quote. That is done as follows:

=CONCATENATE("""";"In quotes!";"""")
Jeankowkow
  • 752
  • 10
  • 28
user4669140
  • 9