Please enable JavaScript to view this site.

Altova DatabaseSpy 2020 Professional Edition

When you run SELECT statements in the SQL Editor, the retrieved data is displayed in the Result window (or multiple Result windows, if multiple queries were executed). You can change the appearance of retrieved data (such as color, background, font weight) based on custom SQL conditions. For example, you can have certain values in the Result grid formatted with yellow background if they exceed some threshold.


The image below illustrates an example of conditional formatting in a SQLite database. This example project is available at the following path: C:\Users\<username>\Documents\Altova\DatabaseSpy2020\DatabaseSpyExamples\Tutorial\. In this example, the goal is to apply conditional formatting as follows:


Records where the quantity exceeds 200 must have coral background and bold font

Records where the quantity is less than 10 must have turquoise background and italic font.


Conditional formatting in SQLite syntax

As illustrated above, in order to support conditional formatting, the SQL statement must include an alias column that must be conventionally named DbSpyRowSettings. This instructs DatabaseSpy to process this column as conditional formatting instructions. Note that the alias column consists of a "CASE" construct. The WHEN branch is the SQL expression where you supply the condition to check for, in the corresponding database grammar. The THEN branch is a string that specifies formatting styles such as font weight or background color. This string must be quoted, in the grammar specific to the database, so that it looks like a string to the database.


The SQL syntax that defines an alias column depends on the database type. Refer to the documentation of the database vendor for details.


The styles you can use are as follows:





Text styles

Text can formatted as bold, italic or underline.




Text colors

Text color can be expressed as a hexadecimal value (case insensitive) or as a named color. For reference to all the named colors you can use, see, Section 4.2.

color: yellow

color: #FFFF00

Background colors

Background colors can have the same values as text colors.

background-color: gold

background-color: #FFD700




You can use multiple styles separated by semi-colons, for example: background-color:gold; font: bold; color: blue;. Whitespace between styles is not relevant.

If the styles you entered are not recognized, DatabaseSpy will ignore them without displaying any errors or warnings.

The DbSpyRowSettings column may appear at any location in the column list.

The case of the DbSpyRowSettings column or the case of styles is not relevant.

When a conditionally formatted cell, column or row is in focus (selected), the background color has no effect. Only text color and font are applied.

For better display results, you might want to clear the Show grid with alternating colors check box in the Result View options.


Conditional formatting in various database kinds

The following are some database-specific examples of SQL statements that use conditional formatting. Note that the examples below illustrate just some of the ways to apply conditional formatting. If supported by the database, it is also possible to achieve the same result with more complex techniques, such as subqueries, stored procedures, and so on.


Database Kind



Use the SWITCH function, see


SELECT [id],
      SWITCH ([quantity] > 200, 'color:blue') AS [DbSpyRowSettings]
FROM   [products];


Alternatively, use the IIF function, see


SELECT [id],
      IIF ([quantity] > 200, 'color:blue', '') AS [DbSpyRowSettings]
FROM   [products];


IBM DB2 for i



Progress OpenEdge




SELECT "id",
      CASE WHEN "quantity" > 200 THEN 'color:blue;' END AS "DbSpyRowSettings"
FROM   "products";

IBM Informix

      CASE WHEN quantity > 200 THEN 'color:blue;' END AS DbSpyRowSettings
FROM   products;



SELECT `id`,
    CASE WHEN `quantity` > 200 THEN 'color:blue;' END AS `DbSpyRowSettings`
FROM   `products`;

SQL Server

SELECT [ProductID],
[DbSpyRowSettings] = CASE WHEN [Quantity] > 200 THEN 'color:blue;' END
FROM   [Products];

© 2019 Altova GmbH