Query with some blank fields

In my SQL database, I have a query that is designed to make display names of the data. It is combining several columns from the table into one field with the following code:

SELECT "ID" || '-' || "Name" || '-' || "Tag Ear" || '-' || "Tag Color" || '-' || "Tag #" FROM "Stock"

It is working well except that not every row in the table has data in the “Name” field. In those cases those rows do not display in the query.

Is there a way to add a space or some sort of placeholder for those instances in order to still display the rest of the information?

Thank you


Please note that SQL is not a database but a language used to communicate with a database. Base has available as a default ether HSQLDB embedded or Firebird embedded databases. Each database may have variations of SQL but most follow a standard. Unfortunately the HSQLDB embedded version (v1.8) included with Base is very old and lacks many features available in newer databases.

That is a good distinction to recognize. I appreciate your explanation.


When dealing with NULL fields you can use the Coalesce function. It will use the first non-Null value found:

....|| Coalesce("Name",'none provided') ||...

You can substitute 'none provided' with whatever you wish including a space - ' '. Do this whereever you have a problem.

This worked. Thank you very much!


Please help others to know the question has been answered by clicking on the :heavy_check_mark: in upper left area of answer which satisfied the question.