Colored query results in SQLcl

For quite a while now, SQLcl has supported coloring query results.

To have colored text in a field, the magic delimiters @| and |@ need to be used. After the starting delimiter (@|) you need to provide one or more directives to color the text that follows (after a separating space) and close the colored text with |@.

For example

There are only a few colors that can be used: black, green, yellow, blue, magenta, cyan and white.

As stated above, multiple directives can be used. This is necessary to control the display attributes: blinking (fast, slow or off), concealing (on/off), intensity (bold, faint or no change), italic (on/off), negative (on/off).

To get a blinking, italic, red field:

select '@|BLINK_FAST,red,italic This is red data|@' colored_text from dual;

The order of the directives is not important, except for directives of the same kind: From 2 or more colors, only the last one will be in effect. BLINK_FAST followed by BLINK_OFF will result in non-blinking text.

The allowed attributes are

• blink:

  • BLINK_FAST

  • BLINK_SLOW

  • BLINK_OFF

• conceal:

  • CONCEAL_ON (results in hidden letters, i.e. only the background is visible)

  • CONCEAL_OFF

• intensity

  • INTENSITY_BOLD

  • INTENSITY_FAINT

• italic

  • ITALIC

• negative

  • NEGATIVE_OFF

  • NEGATIVE_ON

• reset

  • RESET (undoos all the settings to the left of it. Since the colors are set per field, it should never be necessary)

Instead of using just one (foreground) color, it is of course possible to set a background color too: bg_black, bg_green, bg_yellow, bg_blue, bg_magenta, bg_cyan and bg_white. When setting both foreground and background colors it is best to use fg_black, fg_green, fg_yellow, fg_blue, fg_magenta, fg_cyan and fg_white instead of the version without the fg_ prefix. Since the two notations for the foreground colors are equivalent, the following happens:

Hey Jochen, that yellow looks kinda green! Yup, that is because while SQLcl tells the terminal to show yellow, it is still up to the terminal to decide what that will actually look like. I'm using a "Solarized Dark"-setting in Windows Terminal, hence the strange colors.

There are other things that simply don't work in my terminal: slow and fast blinking texts blink at exactly the same rate. Bold turns the text gray, whatever its original color was...

To find out what combinations give what effect, it is possible to come up with a few queries:

with
   colors as (
      select 'black' value from dual union all
      select 'green'       from dual union all
      select 'yellow'      from dual union all
      select 'blue'        from dual union all
      select 'magenta'     from dual union all
      select 'cyan'        from dual union all
      select 'white'       from dual
   )
 , blink as (
      select 'BLINK_FAST' setting from dual union all
      select 'BLINK_OFF'          from dual union all
      select 'BLINK_SLOW'         from dual union all
      select ''                   from dual --not used
   )
 , conceal as (
      select 'CONCEAL_OFF' setting from dual union all
      select 'CONCEAL_ON'          from dual union all
      select ''                    from dual --not used
   )
 , intensity as (
      select 'INTENSITY_BOLD' setting from dual union all
      select 'INTENSITY_FAINT'        from dual union all
      select ''                       from dual --not used
   )
 , italic as (
      select 'ITALIC' setting from dual union all
      select ''               from dual --not used
   )
 , negative as (
      select 'NEGATIVE_OFF' setting from dual union all
      select 'NEGATIVE_ON'          from dual union all
      select ''                     from dual --not used
   )
/* , reset as (
    select 'RESET' setting from dual union all
    select ''              from dual --not used
RESET removes all attributes (italic, blinking,...) to the left of it, leaving only those that follow
)*/
 , underline as (
      select 'UNDERLINE' setting from dual union all
      select 'UNDERLINE_DOUBLE' from dual union all
      select 'UNDERLINE_OFF' from dual union all
      select '' from dual /*no attribute*/
   )
select lpad(c.value, 7, ' ')
       || nvl2(bl.setting, ',' || bl.setting, bl.setting)
       || nvl2(co.setting, ',' || co.setting, co.setting)
       || nvl2(iy.setting, ',' || iy.setting, iy.setting)
       || nvl2(it.setting, ',' || it.setting, it.setting)
       || nvl2(ne.setting, ',' || ne.setting, ne.setting)
       || nvl2(un.setting, ',' || un.setting, un.setting) as color_setting
     , '@|' || c.value
       || nvl2(bl.setting, ',' || bl.setting, bl.setting)
       || nvl2(co.setting, ',' || co.setting, co.setting)
       || nvl2(iy.setting, ',' || iy.setting, iy.setting)
       || nvl2(it.setting, ',' || it.setting, it.setting)
       || nvl2(ne.setting, ',' || ne.setting, ne.setting)
       || nvl2(un.setting, ',' || un.setting, un.setting)
       || ' Test Me|@' as show_color
  from      colors    c
 cross join blink     bl
 cross join conceal   co
 cross join intensity iy
 cross join italic    it
 cross join negative  ne
 cross join underline un
 order by c.value desc nulls first
        , bl.setting nulls first, co.setting nulls first
        , iy.setting nulls first, it.setting nulls first
        , ne.setting nulls first, un.setting nulls first;

The query above gives all (foreground) colors, combined with all attributes. This turns out to result in 6048 combinations.

It can be extended to also use foreground and background colors:

with
   foreground as (
      select 'fg_black' value from dual union all
      select 'fg_green'       from dual union all
      select 'fg_yellow'      from dual union all
      select 'fg_blue'        from dual union all
      select 'fg_magenta'     from dual union all
      select 'fg_cyan'        from dual union all
      select 'fg_white'       from dual
   )
 , background as (
      select 'bg_black' value from dual union all
      select 'bg_green'       from dual union all
      select 'bg_yellow'      from dual union all
      select 'bg_blue'        from dual union all
      select 'bg_magenta'     from dual union all
      select 'bg_cyan'        from dual union all
      select 'bg_white'       from dual
   )
 , blink as (
      select 'BLINK_FAST' setting from dual union all
      select 'BLINK_OFF'          from dual union all
      select 'BLINK_SLOW'         from dual union all
      select ''                   from dual --not used
   )
 , conceal as (
      select 'CONCEAL_OFF' setting from dual union all
      select 'CONCEAL_ON'          from dual union all
      select ''                    from dual --not used
   )
 , intensity as (
      select 'INTENSITY_BOLD' setting from dual union all
      select 'INTENSITY_FAINT'        from dual union all
      select ''                       from dual --not used
   )
 , italic as (
      select 'ITALIC' setting from dual union all
      select ''               from dual --not used
   )
 , negative as (
      select 'NEGATIVE_OFF' setting from dual union all
      select 'NEGATIVE_ON'          from dual union all
      select ''                     from dual --not used
   )
/* , reset as (
    select 'RESET' setting from dual union all
    select ''              from dual --not used
RESET removes all attributes (italic, blinking,...) to the left of it, leaving only those that follow
)*/
 , underline as (
      select 'UNDERLINE' setting from dual union all
      select 'UNDERLINE_DOUBLE' from dual union all
      select 'UNDERLINE_OFF' from dual union all
      select '' from dual /*no attribute*/
   )
select lpad(b.value || ',' || f.value, 21, ' ')
       || nvl2(bl.setting, ',' || bl.setting, bl.setting)
       || nvl2(co.setting, ',' || co.setting, co.setting)
       || nvl2(iy.setting, ',' || iy.setting, iy.setting)
       || nvl2(it.setting, ',' || it.setting, it.setting)
       || nvl2(ne.setting, ',' || ne.setting, ne.setting)
       || nvl2(un.setting, ',' || un.setting, un.setting) as color_setting
     , '@|' || b.value || ',' || f.value
       || nvl2(bl.setting, ',' || bl.setting, bl.setting)
       || nvl2(co.setting, ',' || co.setting, co.setting)
       || nvl2(iy.setting, ',' || iy.setting, iy.setting)
       || nvl2(it.setting, ',' || it.setting, it.setting)
       || nvl2(ne.setting, ',' || ne.setting, ne.setting)
       || nvl2(un.setting, ',' || un.setting, un.setting)
       || ' Test Me|@' as show_color
  from      foreground f
 cross join background b
 cross join blink      bl
 cross join conceal    co
 cross join intensity  iy
 cross join italic     it
 cross join negative   ne
 cross join underline  un
 --where f.vlaue != b.value --same background and foreground color sounds useless, but with attributes set it just might display something
 order by f.value asc nulls first, b.value desc nulls first --desc+asc so the equal bg and fg is not first.
        , bl.setting nulls first, co.setting nulls first
        , iy.setting nulls first, it.setting nulls first
        , ne.setting nulls first, un.setting nulls first;

This gives 42336 combinations, but many of them are useless or look exactly the same as other combinations.

It is possible to use multiple colors in one field:

Coloring can be used to dynamically highlight values:

Remember that the ANSI color codes (that is what the @| is inserting to the string) can only be added to string values.

Note: Generic highlighting (for all queries, without writing @| in them) is better achieved using
set sqlformat ansiconsole -config=highlight.json
but that is a completely separate topic.

Links

The Trivadis Formatter and instructions on how to install it can be found at
https://github.com/Trivadis/plsql-formatter-settings

That Jeff Smith's blog post regarding SQLcl:
https://www.thatjeffsmith.com/archive/tag/sqlcl/
There is no better source than the source itself!