Escape SQL strings

As part of my development job, I often create and troubleshoot SQL queries. And one of such tasks is validating SQL data to make sure the query would return the correct data set as expected.

Time and again, I would run into queries like:

Select blah blah From Table1 Where FieldName In ('abc', 'efd'.....)

Dealing with a short list doesn't involve a lot of typing so it's not a big deal. But when facing a large list, this can be a daunting task as you have to escape the strings and separate them by commas.

This is where Excel spreadsheet comes in handy. In Excel you can do a simple formula to take care of this by typing the following in a cell:

="'" & A1 & "',"

What this does is it takes the value in cell A1 and wrapped it with a single quote and then adds a comma at the end. You can replicate this formula to other cells and it should save you quite a bit of typing.

If you have a list of strings like:

abc
edf
123

all you have to do is copy and paste it in the Excel spreadsheet created with the formula above and you will have a list of escape strings that you can place in your IN criteria. I use this a lot and it saves me a whole lot of typing.

Don't forget to take out the last comma. See attached file for more info.