On this page

Documentation / How do I…: How do I use a list of values from the clipboard in a SQL query?

How do I use a list of values from the clipboard in a SQL query?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

Someone sends you a list of identifiers or values: in an email, a report, a spreadsheet column, a support request or the screen of another application. You need the matching rows, and retyping the list, or adding quotes and commas around each value by hand, is slow and error prone.

You will copy the list, one value per line, and write <@clipboard> in your query where the list belongs. BroadSQL turns the copied lines into a quoted, comma separated SQL list before the query runs, and the query returns the rows for exactly the values you copied.

Short answer

Copy the values, one per line, then write IN <@clipboard> in the query: WHERE CODE IN <@clipboard>.

Example

A colleague asks for the independence year and population of five countries, and sends their three-letter codes, one per line. Select these lines in the email (or the cells of a single spreadsheet column) and copy them with Ctrl+C:

Text to copy to the clipboard

FRA
DEU
ITA
ESP
PRT

Then, connected to WORLD, type the query with <@clipboard> after IN:

Type at the BroadSQL prompt

SELECT CODE, NAME, INDEPYEAR, POPULATION FROM COUNTRY WHERE CODE IN <@clipboard> ORDER BY POPULATION DESC;

Before the query is sent to the database, BroadSQL replaces <@clipboard> with the copied values, each one quoted, inside parentheses: the database receives WHERE CODE IN ('FRA','DEU','ITA','ESP','PRT'). The result has one row per code:

BroadSQL session: after each prompt, what you type; the other lines are what BroadSQL prints

WORLD> SELECT CODE, NAME, INDEPYEAR, POPULATION FROM COUNTRY WHERE CODE IN <@clipboard> ORDER BY POPULATION DESC;
|----|----------------------------------------------------|---------|-----------|
|CODE|NAME                                                |INDEPYEAR|POPULATION |
|----|----------------------------------------------------|---------|-----------|
|DEU |Germany                                             |1955     |82164700   |
|FRA |France                                              |843      |59225700   |
|ITA |Italy                                               |1861     |57680000   |
|ESP |Spain                                               |1492     |39441700   |
|PRT |Portugal                                            |1143     |9997600    |
|----|----------------------------------------------------|---------|-----------|

5 rows fetched in 6 ms.

Illustrative output: the values come from the WORLD sample database and the layout from this release; it was not captured from a BroadSQL run, and your timings will differ.

How BroadSQL reads the clipboard

The clipboard is read as plain text and split into lines. Each line is trimmed, and blank lines are skipped, so a trailing empty line or indented values do no harm.
Every value is written as a quoted SQL string, with an apostrophe inside a value doubled: a copied O'Brien becomes 'O''Brien'. Values are never turned into numbers, so a code such as 00123 keeps its leading zeros.
The list includes its parentheses: write IN <@clipboard>, not IN (<@clipboard>).
The clipboard is read when the statement runs: copy a new list and run the same query again to get its rows.

Variations

Exclude the values: WHERE CODE NOT IN <@clipboard>.
Numbers: a list of city IDs works the same way, WHERE ID IN <@clipboard>: the database compares each quoted value with the number.
The list is in a file: IN <@c:\temp\codes.txt>, one value per line; blank lines are skipped.
The list is a column of a spreadsheet you keep: see Check a colleague's spreadsheet list against the database.
The list is in the result you just displayed: IN <@last:<column>>, shown step by step in Investigate a support ticket and keep the evidence.

Watch out

One column only: when a copied line contains a tab (two spreadsheet columns copied together), BroadSQL stops with the error Clipboard contains multiple columns. <@clipboard> expects a single column. instead of guessing which column to use.
An empty or non-text clipboard gives an empty list, and BroadSQL refuses to run the query rather than send an empty IN () that would silently match nothing.
The values are matched as they are written: a copied fra matches fra only if the column compares without regard to case.
Fixed-length text columns (CHAR) hold their values padded with spaces. On WORLD, whose names are CHAR columns, an IN list of several names matches nothing on NAME itself: compare the trimmed value instead, WHERE TRIM(NAME) IN <@clipboard>. The three-letter codes are full length, so CODE IN <@clipboard> needs no TRIM.

Found something wrong or unclear on this page? Tell us, with the page address.