Ask Your Question
0

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

asked 2019-10-10 23:25:33 +0100

joppla gravatar image

updated 2019-10-11 13:08:09 +0100

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?

edit retag flag offensive close merge delete

Comments

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).

Ratslinger gravatar imageRatslinger ( 2019-10-11 01:18:32 +0100 )edit

db, os, lo version added.

joppla gravatar imagejoppla ( 2019-10-11 13:08:57 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2019-10-10 23:54:14 +0100

Ratslinger gravatar image

Hello,

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

SELECT CONCAT(`SurName`, ', ', COALESCE(`FirstName`,'')) AS `FullName`,`MemberID` FROM `Members`
edit flag offensive delete link more

Comments

Thanks! It works.

joppla gravatar imagejoppla ( 2019-10-11 13:01:20 +0100 )edit

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.

keme gravatar imagekeme ( 2019-10-11 13:36:24 +0100 )edit

CONTCAT_WS gives also nice possibilities! Thanks!

joppla gravatar imagejoppla ( 2019-10-11 13:50:19 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-10 23:25:33 +0100

Seen: 41 times

Last updated: Oct 11