Formatting

in progress

Give each column the presentation and width it deserves, automatically whenever the query runs.

Formatting directives describe how one column should look - numbers, dates, booleans, text, and more - and apply every time the query runs. Right-click a column header and choose Format › Set Formatting Override… to have SQLly write the comment for you, or type one by hand when you prefer.

Syntax

-- sqlly format: column=<name>; <rule>[; <option>...][; width=<n>dp]
-- sqlly format: column=<name>; width=<n>dp

Spelling is strict here. Unlike the other directives, formatting needs the exact prefix -- sqlly format:: a space after -- and a colon immediately after format. --sqlly format: will not apply, and the editor will let you know.

The first segment is always column=<name>. The second is the format rule, a width=, or both:

Rule	Effect	Example
`number=N`	Fixed-point with N decimals (honors `thousands=`, `style=`). The decimal and thousands characters, negative format, and alignment still come from Settings, and the column is never abbreviated.	`number=2`
`date=P`	Render a date, datetime, or date-like text value with strftime pattern P, in the column's display time zone. Values that do not read as dates, and patterns with unknown tokens, leave the cell as sent.	`date=%Y-%m-%d`
`boolean=T/F`	Text for true / false values.	`boolean=Yes/No`
`binary=`	Render binary values as `hex` or `base64`.	`binary=hex`
`text=`	Casing: `upper`, `lower`, `title`, `none`.	`text=upper`
`mask=P/S`	Mask the middle, keeping P leading and S trailing characters. Applies to copies and exports too while Apply mask formatting to copies and exports is on.	`mask=0/4`
`epoch=`	Epoch integer to date: `auto`, `s`, or `ms`.	`epoch=s`
`json=P`	Extract a JSON path from the cell.	`json=$.customer.name`
`template=T`	Templated cell with pipe helpers.	`template={value\|upper}`

Trailing options: thousands=true|false and style=fixed|percent|bytes refine a number= rule.

Column width

width= sets how wide the column is. It can be the only setting on the line, or sit next to a rule:

-- sqlly format: column=Notes; width=60dp
-- sqlly format: column=Total; number=2; width=40dp

The unit is dp, and it follows the grid's font: 1 dp is a quarter of the result grid's font size. At a 10 pt grid font, 20dp is 50 pixels; at 12 pt the same 20dp is 60 pixels. So when you change the font size or zoom, the column grows or shrinks with the text and keeps showing about the same characters - roughly 2.5 dp per character. That is a close approximation rather than an exact count, because the letters of a proportional font are not all the same width. The dp suffix is optional: width=40 means width=40dp.

A width in a directive wins over a width you dragged and SQLly remembered, and over auto-size: auto-size leaves that column alone. If you drag the column to a new width, the directive puts it back on the next run; to keep the new width, click the lock and SQLly rewrites the width= for you.

Several lines for one column

You can write more than one format: line for the same column, and they merge. A later rule replaces an earlier rule, and a later width replaces an earlier width, so these two lines give Total two decimals and a 40 dp width:

-- sqlly format: column=Total; number=2
-- sqlly format: column=Total; width=40dp

Examples

-- sqlly format: column=Total; number=2
-- sqlly format: column=Qty; number=0; thousands=false
-- sqlly format: column=Created; date=%Y-%m-%d
-- sqlly format: column=IsActive; boolean=Yes/No
-- sqlly format: column=rate; number=1; style=percent
-- sqlly format: column=Card; mask=0/4
-- sqlly format: column=Notes; width=80dp
SELECT * FROM orders;

A SQLly formatting directive applied to a result column — A formatting directive applied to a column, with the formatted values shown in the grid.

Every rule except mask= changes only what the grid draws: copy and export carry the database's own values. See masking.

Conditional formatting

format: decides how every value in a column is written. To color cells by their values - red text for Failed, a fill for negative balances, a color scale or a data bar - use the conditional: directive, one rule per line next to your format: lines. The complete guide, including the Conditional Formatting dialog, is Results › Conditional formatting.

-- sqlly format: column=Balance; number=2; width=36dp
-- sqlly conditional: column=Balance; when=< 0; fill=#FDE7E9
SELECT Account, Balance FROM ledger;