Select Vertical Text in SSMS: Alt Shift and Arrow Keys

To select vertical text in SSMS, hold Alt and drag with the mouse. You can also hold Alt and Shift and press the arrow keys. The selection is a rectangle, and anything you type goes onto every line of it.

What a Vertical Selection Does

A normal selection runs along lines. It starts at one point and ends at another, and it takes everything between them. A vertical selection takes a rectangle instead. It picks the same columns on many lines. It is also called a box selection or a column selection.

The tip came from Nicklas Bågvinge, a SQL Server expert I met at a training in Gothenburg. Many people select vertical text by hand, one line at a time. A box makes it a single step. The same keys work in Visual Studio, Notepad++, Word and other editors.

Three Ways to Make the Selection

• Mouse. Hold Alt, press the left button at one corner and drag to the opposite corner. For the mouse drag and four more edits, read Box Selection in SSMS: Four Edits With the ALT Key.
• Keyboard. Put the caret where the box starts. Hold Alt and Shift, and press the arrow keys. Your hands never leave the keyboard.
• Zero width. Place the caret, hold Alt and Shift, and press Down. The box has no width, so it acts as an insertion point on every line it covers.

The zero-width box is the version that saves the most time. You place one caret, extend it down, and type once.

Pick the way that suits the job. The mouse is quick when the whole box is visible on the screen. The keyboard is better for a precise box, such as one column on forty lines. Each arrow key moves one step. Release the keys when the box looks right, and begin to type.

Edit Many Lines at Once

With a box selected, the usual editing keys act on all lines together. Type, and the text appears in the same column of every line. Press Delete, and the selected columns disappear from every line. Copy, and you take the rectangle alone, without the text to its left and right.

This fits the jobs that people repeat all day. You can add a schema name before every table name in a list. You can add a comma at the end of every line. You can delete a leading number column from a pasted report.

Turn a Pasted Column Into an IN List

Suppose a spreadsheet gives you three cities, one per line. You need them as text values inside an IN list. The pasted column looks like this.

Austin
Denver
Chicago

Place a zero-width box before the first letter and extend it down three lines. Type the opening of each value, which is N and a quote. Do the same at the end of the lines, and type a quote and a comma. Remove the last comma and add the rest of the statement. The next query is the finished result. The four-row table in it stands in for a real table of stores.

SELECT s.StoreName, s.City
FROM (VALUES (N'Main Street', N'Austin'), (N'Oak Avenue', N'Denver'), (N'Pine Road', N'Boston'), (N'Elm Court', N'Chicago')) AS s (StoreName, City)
WHERE s.City IN (
    N'Austin',
    N'Denver',
    N'Chicago'
);

Three of the four stores match. The box replaces the retyping of every line.

When T-SQL Does It Better

You could argue that a long list needs no editing at all. Paste the whole block into one string, and let STRING_SPLIT cut it into lines. The script needs SQL Server 2016 for STRING_SPLIT and 2017 for TRIM. Pasted text on Windows has a carriage return before each line feed, so the query removes it.

DECLARE @Pasted nvarchar(max) = N'Austin
Denver
Chicago';
SELECT s.StoreName, s.City
FROM (VALUES (N'Main Street', N'Austin'), (N'Oak Avenue', N'Denver'), (N'Pine Road', N'Boston'), (N'Elm Court', N'Chicago')) AS s (StoreName, City)
WHERE s.City IN (SELECT TRIM(REPLACE(value, CHAR(13), N'')) FROM STRING_SPLIT(@Pasted, CHAR(10)));

It returns the same three rows. The T-SQL version wins for long lists that change every day. The box wins when you edit code, such as a column list or a set of statements. The result has to stay readable text.

What to Remember

To select vertical text, hold Alt and drag, or hold Alt and Shift and use the arrow keys. A box with no width puts one insertion point on many lines. Use it for quotes, commas, schema names and deleted columns. Check where the box starts before you type, so the text lands in the right column. If the list is long and changes daily, let STRING_SPLIT do the work.

A vertical selection is not a trick for selecting, it is a way to edit many lines in one move.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.