Changing the display format of a column of a table in DB Browser for SQLite

In this article I would like to tell you how to change the display format of a column of a table in the DB Browser for SQLite program, and I will tell you why I use it.

In a programme DB Browser for SQLite I often have to manually edit some table values. But the problem is that some table fields are references to the ID field of another table. In this case, it is difficult to understand which row of the table the link is going to. It would be convenient to see instead of ID the value of some column of the table that is being referenced by ID.

I'll explain with an example

There is a Database in which there is a table Mark, in which there is a column (or field) mCategory, which refers to the ID column from the Category table. But the problem is that it is not entirely clear which line it refers to:

In order to see which row from the table Category referenced column Mark.mCategory need to constantly open the table Category and search for a string by ID, which is extremely inconvenient and takes a lot of time.

I would like the Mark table to show in the mCategory column cName column values from the table Category (not ID). To get it as shown in the following screenshot:

Searching for a solution to this problem on the Internet did not yield anything useful, so I decided to share this howto so that you can conveniently and clearly edit tables in the DB Browser for SQLite program. I hope this article is useful to you.

How to do it

1) in the “Data” tab, select a table Mark,

2) right-click on the mCategory column and select Формат отображения

3. A window will appear where you can specify the display format

In the drop-down menu, instead of the “Default” option, you can select different column formats. Here are some of these formats:

Select the “My format” option and enter the following query:

printf('%s', (SELECT cName FROM Category WHERE Category.ID ="mCategory")) 

How it looks in the window:

As we see this SQLite query uses the function printf(), which formats a string to return a formatted result. Let's see what it does:
SELECT cName FROM Category WHERE Category.ID =“mCategory”:
This part of the query selects the value of the column cName from the table Categorywhere the column value ID equals "mCategory", which will be substituted for the value of each row from the column mCategory.
printf('%s', …):

Function printf() takes a formatted string and arguments to insert into that string.

In this case, it uses the format %swhich means it expects a string argument.

Instead of an ellipsis (...) the result of the first part of the query is inserted.

So in the end this query will select the value of the cName column from the Category table where the ID is "mCategory"and format it as a string with printf().

As a result we will get the desired result, which will make it easy to manually edit the necessary fields of the Mark table, having the much-needed information from the column mCategory:

It should be noted that in our example, there is no need to change the value from the column mCategory , because if we do this, then our formatted value will be written instead of ID, and this will be an error. We can freely edit other columns (or fields), seeing what they relate to, thanks to the formatted field mCategory.

I hope you find this solution in this article useful if you often have to manually edit data from a SQLite table.