List of string literals for IN or NOT IN

Bamajohn

There are times when I've cut and pasted a group of numbers or values from a user's email or from a spreadsheet. Typically these are the "natural" key values in our system, usually either QuoteID or InvoiceID. Both are stored as Varchars, so I need to single-quote and comma-separate the list.

I'll copy and paste something that looks like this:
3311560
3420469
3543038
3545980
3489081
3545978

I need to make it into an "IN" or "NOT IN" phrase in SQL, like so:
WHERE QuoteID IN (
'3311560'
,'3420469'
,'3543038'
,'3545980'
,'3489081'
,'3545978'
)

Today, what SQL Prompt 6.3 (and all previous releases) does as I walk the cursor up and down this list using rapid <single quote><up><left> or <single quote><down><right> keystrokes is occasionally double the single quotes at the end of each line, or worse, pop up a suggestion box while I'm just trying to key in a comma at the beginning or end of each line.

Why can't SQL prompt "notice" a list of values inside an "IN()" or "NOT IN()" and offer to quote and comma separate them for me? Or at least not get in my way while I'm editing them manually?

David Priddle

Hi Bamajohn,

I know this is a workaround and not a solution, but it let you continue working in the short term. If you make a vertical selection (holding down alt and then dragging down across lines), you can modify every line at once, plus the suggestions window doesn't come up:

The selection:

Single quote typed at end of list:

Comma and single quote typed at start of list:

First comma removed from list (after coming out of vertical selection):


I'll have a chat with the rest of the team to see if we can get around your problem for good.

Best regards,

David

Bamajohn

Wow. I had used alt-select to execute something commented out before, but I had no idea it could be used like that. That's a pretty amazing workaround! Thanks!

Also, while you are looking at this, is there a way to separate the way SQL Prompt reformats comma lists such that IN and NOT IN lists could be treated differently than the list of fields in the SELECT list? I really like having each field on its own line in the SELECT, but there are times I want IN() and NOT IN () lists to stay on one line, or word-wrap normally.

David Priddle

It's good fun isn't it! The only problem with it is if the lines are of a different length.

Could I ask you to vote on the currently-existing feature request for pasted-in lists: http://redgate.uservoice.com/forums/944 ... ed-in-list

We use UserVoice to prioritise features we're going to be working on (along with technical complexity).

Unfortunately, we don't have the capability to format these two clauses differently when formatting.

We're hoping to start some work around formatting this year. Within the next few weeks, we'll be asking the community for some example scripts for formatting preferences. This will allow us to focus our efforts to create a better system. If you could take part in that, we would be extremely grateful!

Best regards,

David

Bamajohn

I have voted it up and looked at some others in there. I guess I was unaware of this UserVoice prioritization system.

When you say you'll be asking--how / where will the asking take place? Over email? In the forums? I'll be glad to help where possible...

David Priddle

Hi Bamajohn,

Thanks for your time! We've just placed a sticky which shed a little more light on UserVoice.

Our intention is to publicise the request for scripts / styles on this forum. We'll probably start doing this in the next week or two. Our aim is to find out what changes are going to be needed to the formatting system before we start.

Best regards,

David

David Priddle

Hi Bamajohn,

We've just opened up the forum to get some examples of SQL scripts formatted to your ideal style. If it's still OK with you, it would be great if you could take part!

The announcement post, and link to the questionnaire is here

Best regards,

David