2

In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk.

Is there a similar keyboard function that applies single quotes across a list of values? I have 150 IDs that I have copied from an Excel spreadsheet and I would like to execute a query that uses an IN clause on this list, and I dont want to manually add single quotes and comma around each item.

For example:

SELECT * FROM tbl WHERE someValue IN (
ABC1
ABC2
ABC3
ABC4
)

I want to highlight the values and using a keyboard shortcut turn the list into:

SELECT * FROM tbl WHERE someValue IN (
'ABC1',
'ABC2',
'ABC3',
'ABC4'
)
10

Native tooling approach

Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this.

  1. Put your cursor right before the first leading ABC.
  2. Hold Shift+Alt, then hit the down arrow three times.
  3. Type '.

It's subtle, but you should see a faint blue vertical line here indicating that keystrokes will actually affect all 4 lines (or 150 lines).

enter image description here

Excel approach

If you don't want to use a more modern version of Management Studio, then just add the stuff in Excel. Insert a new column, put a single-quote in the first cell where you want it, hover over the bottom right of the cell until the cursor becomes a solid +, then click and drag to the bottom:

enter image description here

Repeat for the second single-quote, commas, etc. Copy all, paste into SSMS, then do a quick replace for '<tab>, <tab>',, etc.

Regular expression approach

Your third alternative is to use a Regular Expression, valid with all versions of SSMS

  • Find what: {.+}
  • Replace with: '\1',
  • Look in: Selection
  • Expand Find Option
  • Use: Regular expression (checked)

That regular expression indicates find everything and remember what we found Replace everything we found \1 by wrapping it with with tic marks and a comma

If you have more complex requirements, the right chevron next to the drop down arrow on Find what lists the regular expression dialect SSMS/Visual Studio understands

Regex what?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.