Format pasted-in list
I need a way to format a group of text items into a SQL query that I'm going to use for an "IN ('item1','item2','item3')" clause within the WHERE clause. There's got to be a simpler way than manually keying in all the single quotes and commas, particularly when the comma triggers SQL Prompt to start trying to prompt each time, so I have to add an Esc keypress to get SQL Prompt out of the way.
John
shared this idea
-
jgonnering
commented
this feature has been available since 7.0 (August 2015). Reference Actions List section of following link: https://documentation.red-gate.com/display/SP7/SQL+Prompt+7.0+release+notes
-
jgonnering
commented
I have to use the spreadsheet method that jbelina mentions, but that is very tedious and requires I leave SSMS. It would be great if I could paste in a list that I copy out of an email, ticket system, etc., and then use Format SQL (CTRL K + CTRL Y) to insert commas to separate them and use the column's data type for the IN clause to determine whether to wrap them in single quotes (date, char data) or not (int data). I do this many times a day and this would be an awesome timesaver. Thanks
-
jbelina commented
Here's a workaround you can use in Excel...
Step:
1) Create a spreadsheet and paste in your list starting in A1.
2) Insert a row above row 1
3) In B1 put the following text: IN (
4) In B2 put the following formula: =CONCATENATE($B1,"'",$A2,"',")
5) Click B2 and Drag the bottom right corner down until it goes as far as your list
6) Click on the last item in column B and change the formula replacing the last comma with a closing parenthesis similar to the following:
=CONCATENATE($B3,"'",$A4,"')")
7) Copy the item you just changed into your SQL Query and you should end up with something like your original clause:
IN ('item 1','item 2','item 3')Works great for a long list... :D