[Base]: Query give no results by reason of empty or null field

[joppla]

Hello,

Used db: mariadb; OS: Windows10 & Xubuntu18.04; LO: 6.3.x

I made a sql query with the following suntax:

SELECT CONCAT(`SurName`, ', ', `FirstName`) AS `FullName`, `MemberID` FROM `Members`

The result is a table with two columns: FullName and MemberID
In de field Fullname I got the name with the syntax: Johnson, Boris or May, Theresa.

But if the field of the FirstName is empty the field with Fullname is empty. If I have f.e. a space in the field FirstName I will see f.e. Johnson, .

How can I make the SQL Query in this way that I don’t need to fill in everywhere a space in a field?

[Ratslinger]

Hello,

Please, when dealing with Base, always mention database used as items such as SQL can vary with database. It is usually helpful to have OS and specific LO version (may have bug fixed is a different release).

[joppla]

db, os, lo version added.

[Ratslinger]

Hello,

You can use COALESCE to use the first non null field:

SELECT CONCAT(`SurName`, ', ', COALESCE(`FirstName`,'')) AS `FullName`,`MemberID` FROM `Members`

[joppla]

Thanks! It works.

[keme]

If it is also possible that the surname is missing, you may need that COALESCE() construct also on the first part in CONCAT().

Current versions of MariaDB supports “concatenate with separator”, CONCAT_WS(), which handles null values gracefully. First parameter is the separator of choice, in this case comma + space:

SELECT CONCAT_WS(', ', `SurName`, `FirstName`) AS `FullName`,`MemberID` FROM `Members`

This does not add leading/trailing comma, which means that it looks cleaner, but you can’t distinguish between e.g. Jackson used as first name and as surname if the other name is missing.

[joppla]

CONTCAT_WS gives also nice possibilities! Thanks!